--Setting the logic version
--Do not forget to update the installer version in code
update meta_version set minor = 3; 

--go

--0--------------------------------------------------------------------------------------------
--Creating all table columns view
create or replace view meta_table_columns
as select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
   'meta_string_columns' as column_type
from meta_string_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_ref_columns' as column_type
from meta_ref_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_enum_columns' as column_type
from meta_enum_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_decimal_columns' as column_type
from meta_decimal_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_datetime_columns' as column_type
from meta_datetime_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_bool_columns' as column_type
from meta_bool_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_binary_columns' as column_type
from meta_binary_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_int_columns' as column_type
from meta_int_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_big_int_columns' as column_type
from meta_big_int_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_small_int_columns' as column_type
from meta_small_int_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_guid_columns' as column_type
from meta_guid_columns c inner join meta_tables t on c.table_id = t.table_id
union
select c.table_id, t.table_name, column_id, column_name, c.display_name, c.description, is_not_null, order_index, customization_level,
    'meta_text_columns' as column_type
from meta_text_columns c inner join meta_tables t on c.table_id = t.table_id;

--go

--Table of user names
create or replace view meta_user_names
as select user_name, display_name from meta_users;

--go

--Current user properties
create or replace view meta_current_user
as select * from meta_users where user_name = lower(user);

--go

--Table of security principals
create or replace view meta_principals
as
select user_name as principal_name, display_name, 'user' as principal_type
from meta_users
union
select role_name as principal_name, display_name, 'role' as principal_type
from meta_roles;

--go

--A view to show user's acl unit-level access rights
create or replace view meta_user_acl_unit_access
as select table_id, unit_id, access_type from meta_table_acl_unit_access
where user_name = lower(user);

--go

--A view to show user's acl full access rights
create or replace view meta_user_acl_full_access
as select table_id, access_type from meta_table_acl_full_access
where user_name = lower(user);

--go

--A view to show user's acl own rows access rights
create or replace view meta_user_acl_own_rows
as select table_id, access_type from meta_table_acl_own_rows
where user_name = lower(user);

--go

--1--------------------------------------------------------------------------------------------
--Function that gets dbms name
create or replace procedure meta_get_dbms_name(
   a_product_name out varchar2)
as
begin
    a_product_name := 'ORACLE';
end;

--go

--Function that gets dbms version
create or replace procedure meta_get_dbms_version(
    a_dbms_version out varchar2)
as
begin
    select version into a_dbms_version
    from product_component_version
    where product like '%Oracle%';
end;

--go

--Function that return database name
create or replace procedure meta_get_current_db_name(
    a_db_name out varchar2)
as
begin
    select sys_context('USERENV', 'DB_NAME') into a_db_name from dual;
end;

--go

--Function of getting table name
create or replace function p_get_table_name(
    --Table id
    a_table_id raw)
    return varchar2
as
    --Table name
    target_table_name varchar2(30) := null;
begin
    if a_table_id is null then
        return null;
    end if;

    select table_name into target_table_name 
    from meta_tables where table_id = a_table_id;
    
    return target_table_name;
end;

--go

--Function of dropping a table
create or replace procedure p_drop_table_if_exists(
    --Target table name
    a_table_name varchar2)
as
    table_count integer;
begin
    select count(*)  into table_count from user_objects
    where object_name = upper(a_table_name)
    and object_type = 'TABLE';
    
    if  table_count > 0 then
        execute immediate 'drop table ' || upper(a_table_name) 
        || ' cascade constraints';
    end if;
end;

--go

--Function of dropping a view
create or replace procedure p_drop_view_if_exists(
    --Target view name
    a_view_name varchar2)
as
    view_count integer;
begin
    select count(*)  into view_count from user_objects
    where object_name = upper(a_view_name)
    and object_type = 'VIEW';
    
    if  view_count > 0 then
        execute immediate 'drop view ' || upper(a_view_name) 
        || ' cascade constraints';
    end if;
end;

--go

--Function of soft deleting a table row
create or replace procedure meta_soft_delete_table_row(
    --Table id
    a_table_id raw,
    --Row id
    a_row_id raw)
as
    --Current row id
    a_curr_row_id raw(16);
    --Current table name
    a_curr_table_name varchar2(26);
    --Current table id
    a_curr_table_id raw(16);
    --Ref table id
    a_ref_table_id raw(16);
    --Ref table name
    a_ref_table_name varchar2(26);
    --Ref column name
    a_ref_column_name varchar2(26);
    --Row count
    a_row_count number;
    --Current user
    a_curr_user varchar2(26);
    --Current date
    a_curr_date date;
begin
    --Getting the current user and date
    a_curr_user := lower(user);
    a_curr_date := sysdate;

    --Saying that we want to delete the initial row
    insert into meta_rows values(a_row_id, a_table_id, 0);
    
    loop    
        --Getting a row to be deleted
        select table_id, row_id
        into a_curr_table_id, a_curr_row_id
        from meta_rows where is_used=0 and rownum < 2;
        
        a_curr_table_name := p_get_table_name(a_curr_table_id);
         
         --Enumerating all references on this table
         for ref in (select column_id, cascade_delete from meta_ref_targets
             where ref_table_id = a_curr_table_id) loop
                 
             --Reading ref column properties
             select column_name, table_id
             into a_ref_column_name, a_ref_table_id
             from meta_ref_columns
             where column_id = ref.column_id;
                 
             a_ref_table_name := p_get_table_name(a_ref_table_id);
             
             if ref.cascade_delete = 0 then
                 --Checking reference value existence
                 execute immediate 'select count(*) from dual where exists(select 1 from tbl_' || a_ref_table_name
                 || ' where ' || a_ref_column_name || '= :1 and is_active=1)' 
                 into a_row_count
                 using a_curr_row_id;
                 
                 --If there are reference values and cascade delete is forbidden
                 if a_row_count > 0 then
                    raise_application_error(-20001,
                        'Deleting when there are references on the row (id = '
                        || a_row_id 
                        || ') with CascadeDelete = false (#error_code = 20001)');
                 end if;
             end if;
             
            --Deleting other reference values too (putting them into the list of the rows to be deleted)
             execute immediate 'insert into meta_rows(row_id, table_id)'
             || 'select id, :1 from tbl_' || a_ref_table_name
             || ' where ' || a_ref_column_name || ' = :2'
             || ' and id not in (select row_id from meta_rows)'
             using a_ref_table_id, a_curr_row_id;
         end loop;
         
        --Deleting the current row
        execute immediate 'update tbl_' || a_curr_table_name
        || ' set is_active = 0, deleted_on = :1, deleted_by = :2 where id=:3'
        using a_curr_date,  a_curr_user, a_curr_row_id;
            
        --Adding the current row to the deleted rows list
        update meta_rows
        set is_used = 1
        where row_id = a_curr_row_id;
        
        select count(*) into a_row_count
        from meta_rows where is_used = 0 and rownum < 2;
            
        exit when a_row_count = 0;    
     end loop;
end;

--go

--Function of restoring a table row
create or replace procedure meta_restore_table_row(
    --Target id
    a_table_id raw,
    --Row id
    a_row_id raw)
as
    --Current row id
    a_curr_row_id raw(16);
    --Current table name
    a_curr_table_name varchar2(26);
    --Current table id
    a_curr_table_id raw(16);
    --Ref table name
    a_ref_table_name varchar2(26);
    --Row count
    a_row_count number;
    --Reference value
    a_ref_value raw(16);
    --Reference value was found
    a_ref_value_was_found number(1) := 0;
begin
    --Saying that we want to restore the initial row
     insert into meta_rows values(a_row_id, a_table_id, 0);
    
    loop    
        --Getting a row to be restored
        select table_id, row_id
        into a_curr_table_id, a_curr_row_id
        from meta_rows where is_used=0 and rownum < 2;

        a_curr_table_name := p_get_table_name(a_curr_table_id);
            
         --Enumerating all references in this table
         for ref in (select column_id, column_name from meta_ref_columns
             where table_id = a_curr_table_id) loop
                 
             --Reading reference value
             execute immediate 'select ' || ref.column_name
             || ' from tbl_' || a_curr_table_name
             || ' where id = :1'
             into a_ref_value
             using a_curr_row_id;
                 
             if a_ref_value is not null then
                    
                a_ref_value_was_found := 0;
                    
                --Enumerating all referenced tables
                for ref_table in (select ref_table_id from meta_ref_targets
                    where column_id = ref.column_id) loop
                        
                    a_ref_table_name := p_get_table_name(ref_table.ref_table_id);
                 
                    --Checking reference value existence
                    execute immediate 'select count(*) from tbl_' 
                    || a_ref_table_name || ' where id = :1'
                    into a_row_count
                    using a_ref_value;
                 
                    --If there are reference values
                    if a_row_count > 0 then
                       --Restoring other reference values too (putting them into the list of rows to be restored)                           
                        execute immediate 'insert into meta_rows(row_id, table_id)'
                        || ' select :1, :2 from dual'
                        || ' where :3 not in (select row_id from meta_rows)'
                        using a_ref_value, ref_table.ref_table_id, a_ref_value;
                        
                        a_ref_value_was_found := 1;
                    end if;
                 end loop;
                     
                 --If reference value was not found
                 if a_ref_value_was_found = 0 then
                    raise_application_error(-20002,
                    'Restoring when there is no referenced row (id = '
                    || a_row_id || ', ref_value = '
                    || a_ref_value
                    || ') (#error_code = 20002)');
                 end if;                        
             end if;
         end loop;
             
         --Restoring the row
        execute immediate 'update tbl_' || a_curr_table_name || ' set is_active = 1,'
        || ' deleted_on = null, deleted_by = null where id = :1'
        using a_curr_row_id;
            
        --Adding the current row to the restored rows list
        update meta_rows
        set is_used = 1
        where row_id = a_curr_row_id;
        
        select count(*) into a_row_count
        from meta_rows where is_used = 0 and rownum < 2;
            
        exit when a_row_count = 0;    
    end loop;   
end;

--go

--Function of hard deleting a table row
create or replace procedure meta_delete_table_row(
    --Target id
    a_table_id raw,
    --Row id
    a_row_id raw)
as
    --Current row id
    a_curr_row_id raw(16);
    --Current table name
    a_curr_table_name varchar2(26);
    --Current table id
    a_curr_table_id raw(16);
    --Ref table id
    a_ref_table_id raw(16);
    --Ref table name
    a_ref_table_name varchar2(26);
    --Ref column name
    a_ref_column_name varchar2(26);
    --Row count
    a_row_count number;
begin
   
    --Saying that we want to delete the initial row
    insert into meta_rows values(a_row_id, a_table_id, 0);
    
    loop    
        --Getting a row to be deleted
        select table_id, row_id
        into a_curr_table_id, a_curr_row_id
        from meta_rows where is_used=0 and rownum < 2;
        
        a_curr_table_name := p_get_table_name(a_curr_table_id);
        
        --Enumerating all references on this table
        for ref in (select column_id, cascade_delete from meta_ref_targets
         where ref_table_id = a_curr_table_id) loop
                         
            --Reading ref column properties
            select column_name, table_id
            into a_ref_column_name, a_ref_table_id
            from meta_ref_columns
            where column_id = ref.column_id;
                         
            a_ref_table_name := p_get_table_name(a_ref_table_id);
                                                  
            --Deleting other reference values too (putting them into the list of the rows to be deleted)
            execute immediate 'insert into meta_rows(row_id, table_id)'
            || 'select id, :1 from tbl_' || a_ref_table_name
            || ' where ' || a_ref_column_name || ' = :2'
            || ' and id not in (select row_id from meta_rows)'
            using a_ref_table_id, a_curr_row_id;
        end loop;
                 
        --Deleting the current row
        execute immediate 'delete from tbl_' || a_curr_table_name
        || ' where id=:1'
        using a_curr_row_id;
                    
        --Adding the current row to the deleted rows list
        update meta_rows
        set is_used = 1
        where row_id = a_curr_row_id;
        
        select count(*) into a_row_count
        from meta_rows where is_used = 0 and rownum < 2;
            
        exit when a_row_count = 0;      
     end loop;
end;

--go

--Function of creating table triggers
create or replace procedure p_create_table_triggers(
    --Table id
    a_table_id raw)
as
    --Table name
    a_table_name varchar2(26);
begin
    a_table_name := p_get_table_name(a_table_id);    

    --Trigger before inserting a row
    execute immediate 'create trigger i'
    || substr(a_table_id, 1, 29) 
    || ' before insert on tbl_' || a_table_name
    || ' for each row'
    || ' begin'
    || ' if :new.id is null then'
    || ' :new.id := sys_guid;'
    || ' end if;'
    || ' if :new.owner is null then'
    || ' :new.owner := lower(user);'
    || ' else'
    || ' :new.owner := lower(:new.owner);'
    || ' end if;'
    || ' :new.created_on := sysdate;'
    || ' :new.updated_on := sysdate;'
    || ' :new.created_by := lower(user);'
    || ' :new.updated_by := lower(user);'
    || ' :new.row_version := sys_guid;'
    || ' end;';
end;

--go

--Function of creating table view
create or replace procedure p_create_table_view(
    --Table id
    a_table_id raw)
as
    --Table name
    table_name varchar2(26);
    --Unit level security
    a_unit_level_security numeric(1);
    --Recycle bin option
    a_recycle_bin_option numeric(10);
    --Comma delimited list of column names
    column_name_list varchar2(32767) := '';
    --Create view sql
    a_create_view_sql varchar2(32767);
    --Update trigger sql
    a_update_trigger_sql varchar2(32767);
    --Delete trigger sql
    a_delete_trigger_sql varchar2(32767);
begin
    table_name := p_get_table_name(a_table_id);
    
    --Reading table properties
    select unit_level_security into a_unit_level_security
    from meta_tables
    where table_id = a_table_id;
    
    --Reading options
    select int_value into a_recycle_bin_option
    from meta_options
    where option_name = 'RECYCLE_BIN';
    
    for col in (select column_name, column_type from meta_table_columns
         where table_id = a_table_id order by order_index) loop
         if col.column_name <> 'is_active' and
            col.column_name <> 'deleted_by' and
            col.column_name <> 'deleted_on' then
            
            if length(column_name_list) > 0 then
                column_name_list := column_name_list || ',';
            end if;
            
            column_name_list := column_name_list || col.column_name;
          end if;
    end loop;

    --Creating the view
    a_create_view_sql := 'create or replace view '
    || table_name
    || ' as select '
    || column_name_list || ' from tbl_'
    || table_name || ' t'
    || ' where 1 = 1';
    
    --If recycle bin is enabled
    if a_recycle_bin_option = 1 then
        a_create_view_sql := a_create_view_sql || ' and is_active = 1';    
    end if;
    
    --If unit level security is ON
    if a_unit_level_security = 1 then
        --The full access
        a_create_view_sql := a_create_view_sql || ' and (exists(select 1 from meta_table_acl_full_access where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=1)'
        --The unit level access
        || ' or t.owner in (select u.user_name from meta_table_acl_unit_access s inner join meta_user_units u on s.unit_id=u.unit_id'
        || ' where table_id=hextoraw(''' || a_table_id || ''') and s.user_name=lower(user) and access_type=1)'
        --The own rows access
        || ' or t.owner=lower(user) and exists(select 1 from meta_table_acl_own_rows where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=1))';
    end if;
    
    execute immediate a_create_view_sql;
    
    --Creating the delete trigger
    a_delete_trigger_sql := 'create or replace trigger d'
    || substr(a_table_id, 1, 29)
    || ' instead of delete on '
    || table_name
    || ' for each row'
    || ' declare'
    || ' result number;'
    || ' begin';
    
    --If unit level security is ON
    if a_unit_level_security = 1 then
        --Checking the delete permission
        a_delete_trigger_sql := a_delete_trigger_sql || ' select count(*) into result from dual'
        --The full access
        || ' where exists(select 1 from meta_table_acl_full_access where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=4)'
        --The unit-level access
        || ' or :old.owner in (select u.user_name from meta_table_acl_unit_access s inner join meta_user_units u on s.unit_id=u.unit_id'
        || ' where table_id=hextoraw(''' || a_table_id || ''') and s.user_name=lower(user) and access_type=4)'
        --The own rows access
        || ' or :old.owner=lower(user) and exists(select 1 from meta_table_acl_own_rows where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=4);'
        || ' if result = 0 then'
        || ' raise_application_error(-20004,'
        || '''Insufficient privileges to delete the row (id = '' || :old.id || '') (#error_code = 20004)'');'
        || ' end if;';
    end if;    
    
    --If recycle bin is enabled
    if a_recycle_bin_option = 1 then
        --Performing the soft delete
        a_delete_trigger_sql := a_delete_trigger_sql || ' meta_soft_delete_table_row(''' || a_table_id || ''', :old.id);';
    else
        --Performing the delete
        a_delete_trigger_sql := a_delete_trigger_sql || ' meta_delete_table_row(''' || a_table_id || ''', :old.id);';
    end if;
    
    a_delete_trigger_sql := a_delete_trigger_sql || ' end;';
    
    execute immediate a_delete_trigger_sql;
    
    --Creating update trigger
    a_update_trigger_sql := 'create trigger j'
    || substr(a_table_id, 1, 29)
    || ' instead of update on ' || table_name
    || ' for each row'
    || ' declare'
    || ' a_set_list varchar2(32767);'
    || ' result number;'
    || ' begin';
    
    --If unit level security is ON
    if a_unit_level_security = 1 then
        --Checking the update permission
         a_update_trigger_sql := a_update_trigger_sql || ' select count(*) into result from dual'
        --The full access
        || ' where exists(select 1 from meta_table_acl_full_access where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=3)'
        --The unit-level access
        || ' or :old.owner in (select u.user_name from meta_table_acl_unit_access s inner join meta_user_units u on s.unit_id=u.unit_id'
        || ' where table_id=hextoraw(''' || a_table_id || ''') and s.user_name=lower(user) and access_type=3)'
        --The own rows access
        || ' or :old.owner=lower(user) and exists(select 1 from meta_table_acl_own_rows where table_id=hextoraw('''
        || a_table_id || ''') and user_name=lower(user) and access_type=3);'
        || ' if result = 0 then'
        || ' raise_application_error(-20003,'
        || '''Insufficient privileges to update the row (id = '' || :old.id || '') (#error_code = 20003)'');'
        || ' end if;';
    end if;
    
    --Performing the update
    a_update_trigger_sql := a_update_trigger_sql || ' update tbl_' || table_name
    || ' set owner=lower(:new.owner), updated_on=sysdate, updated_by=lower(user), row_version=sys_guid';
        
     for col in (select column_name from meta_table_columns
         where table_id = a_table_id and customization_level > 0) loop
        a_update_trigger_sql := a_update_trigger_sql
        || ',' || col.column_name || '=:new.' || col.column_name;
     end loop;
    
    a_update_trigger_sql := a_update_trigger_sql
    || ' where id=:new.id;'
    || ' end;';
    
    execute immediate a_update_trigger_sql;   
    
    --Setting table view grants
    p_set_table_view_grants(a_table_id);
end;

--go

--Function of setting table view grants
create or replace procedure p_set_table_view_grants(
    --Table id
    a_table_id raw)
as
    --Table name
    a_table_name varchar2(26);
begin
    a_table_name := p_get_table_name(a_table_id);

    for acl in (select access_type, principal_name from meta_table_acl
        where table_id = a_table_id) loop
        
        if acl.access_type = 1 then
            execute immediate 'grant select on ' || a_table_name || ' to ' || acl.principal_name;
        elsif acl.access_type = 2 then
            execute immediate 'grant insert on ' || a_table_name || ' to ' || acl.principal_name;
        elsif acl.access_type = 3 then
            execute immediate 'grant update on ' || a_table_name || ' to ' || acl.principal_name;
        elsif acl.access_type = 4 then
            execute immediate 'grant delete on ' || a_table_name || ' to ' || acl.principal_name;
        end if;
        
    end loop;
end;

--go

--Function of checking that table doesn't exist
create or replace procedure p_check_table_not_exists(
    --Table id
    a_table_id raw,
    --Table name
    a_table_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the table id
    select count(*) into a_row_count
    from meta_tables
    where table_id = a_table_id;
    
    if a_row_count > 0 then
        raise_application_error(-20025,
                'A table with the specified id was already added (table_id = '
                || a_table_id
                || ') (#error_code = 20025)');
    end if;
    
    --Searching for the table name
    select count(*) into a_row_count
    from meta_tables
    where table_name = a_table_name;
    
    if a_row_count > 0 then
        raise_application_error(-20026,
                'A table with the specified name was already added (table_name = '
                || a_table_name
                || ') (#error_code = 20026)');
    end if;
    
end;

--go

--Function of checking table existence
create or replace procedure p_check_table_exists(
    --Table id
    a_table_id raw)
as
    --Row count
    a_row_count number;
begin
    --Searching for the table id
    select count(*) into a_row_count
    from meta_tables
    where table_id = a_table_id;
    
    if a_row_count = 0 then
        raise_application_error(-20027,
                'Specified table was not found (table_id = '
                || a_table_id
                || ') (#error_code = 20027)');
    end if;
end;

--go

--Function of creating new user table
create or replace procedure meta_add_table(
    --Table id
    a_table_id raw,
    --Table name
    a_table_name varchar2,
    --Id column id
    a_id_column_id raw,
    --Owner column id
    a_owner_column_id raw,
    --CreatedBy column id
    a_created_by_column_id raw,
    --CreatedOn column id
    a_created_on_column_id raw,
    --UpdatedBy column id
    a_updated_by_column_id raw,
    --UpdatedOn column id
    a_updated_on_column_id raw,
    --DeletedBy column id
    a_deleted_by_column_id raw,
    --DeletedOn column id
    a_deleted_on_column_id raw,
    --IsActive column_id
    a_is_active_column_id raw,
    --RowVersion column id
    a_row_version_column_id raw)
as
begin
    p_check_table_not_exists(a_table_id, a_table_name);

    --Inserting metadata
    insert into meta_tables(table_id, table_name)
    values (a_table_id, a_table_name);

    --Creating user table
    execute immediate 'create table tbl_'
    || a_table_name
    || '(id raw(16) not null, '
    || 'owner varchar2(26) not null, '
    || 'is_active number(1) default 1 not null, '
    || 'created_by varchar2(26) not null, '
    || 'created_on date not null, '
    || 'updated_by varchar2(26) not null, '
    || 'updated_on date not null, '
    || 'row_version raw(16) not null,'
    || 'deleted_by varchar2(26) null, '
    || 'deleted_on date null, '
    || 'check(length(trim(owner))>0),'
    || 'check(is_active=0 or is_active=1))';
    
    --Inserting system columns
    insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_id_column_id, a_table_id, 'id', 1, N'Id', 0, 0, N'The row id.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(a_owner_column_id, a_table_id, 'owner', 1, N'Owner', 1, 0, 26, N'The row owner.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(a_created_by_column_id, a_table_id, 'created_by', 1, N'Created by', 2, 0, 26, N'The name of the user that created the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_created_on_column_id, a_table_id, 'created_on', 1, N'Created on', 3, 0, N'The date and time of the row creation.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(a_updated_by_column_id, a_table_id, 'updated_by', 1, N'Updated by', 4, 0, 26, N'The name of the user that updated the row last.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_updated_on_column_id, a_table_id, 'updated_on', 1, N'Updated on', 5, 0, N'The date and time of the last row update.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(a_deleted_by_column_id, a_table_id, 'deleted_by', 0, N'Deleted by', 6, 0, 26, N'The name of the user that deleted the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_deleted_on_column_id, a_table_id, 'deleted_on', 0, N'Deleted on', 7, 0, N'The date and time of the row deletion.');
    
    insert into meta_bool_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_is_active_column_id, a_table_id, 'is_active', 1, N'Is active', 8, 0, N'True if the row was not soft deleted.');
    
     insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(a_row_version_column_id, a_table_id, 'row_version', 1, N'Row version', 9, 0, N'The version of the row.');
        
    --Creating table view
    p_create_table_view(a_table_id);

    --Creating table triggers
    p_create_table_triggers(a_table_id);
    
    --Granting access to schema administrators
    for u in (select user_name from meta_users
        where can_change_schema = 1) loop
        execute immediate 'grant select, insert, update, delete on tbl_' || a_table_name || ' to ' || u.user_name;
    end loop;    
end;

--go

--Function of deleting user table
create or replace procedure meta_delete_table(
    --Table id
    a_table_id raw)
as
    --Table name
    a_table_name varchar(26);
begin
    p_check_table_exists(a_table_id);

    a_table_name := p_get_table_name(a_table_id);

    --Deleting the metadata
    delete from meta_tables
    where table_id = a_table_id;

    --Dropping table view
   p_drop_view_if_exists(a_table_name);
    
    --Dropping the table
    p_drop_table_if_exists('tbl_' || a_table_name);
end;

--go

--Function of table renaming
create or replace procedure meta_rename_table(
    --Table id
    a_table_id raw,
    --New table name
    a_new_table_name varchar2)
as
    --Old table name
    a_old_table_name varchar2(26);
    --Row count
    a_row_count number;
begin
    p_check_table_exists(a_table_id);
    
    --Searching for a table with the same name
    select count(*) into a_row_count
    from meta_tables
    where table_name = a_new_table_name;
    
    if a_row_count > 0 then
        raise_application_error(-20026,
            'A table with the specified name was already added (table_name = '
            || a_new_table_name
            || ') (#error_code = 20026)');
    end if;
    
    a_old_table_name := p_get_table_name(a_table_id);

    --If there is no need for renaming
    if a_old_table_name = a_new_table_name then
        return;
    end if;
    
    --Dropping table view
    p_drop_view_if_exists(a_old_table_name);
    
    --Renaming the table
    execute immediate 'alter table tbl_' || a_old_table_name
    || ' rename to tbl_' || a_new_table_name;
    
    --Updating the metadata
    update meta_tables
    set table_name = a_new_table_name
    where table_id = a_table_id;
    
     --Renaming all index triggers for table columns
    for item in (select column_id from meta_table_columns where table_id=a_table_id) loop
        --Recreating column index triggers
        p_recreate_col_index_triggers(item.column_id);
    end loop;
    
    --Creating table view
    p_create_table_view(a_table_id);
end;

--go

--Function of setting the availability of the recycle bin
create or replace procedure meta_set_recycle_bin(
    --The availability of the recycle bin
    a_recycle_bin_enabled numeric)
as
begin
    --Updating the metadata
    update meta_options
    set int_value = a_recycle_bin_enabled
    where option_name = 'RECYCLE_BIN';
    
    --Recreating table views
    for t in (select table_id, table_name from meta_tables) loop
        p_drop_view_if_exists(t.table_name);
        p_create_table_view(t.table_id);
    end loop;
end;

--go

--2--------------------------------------------------------------------------------------------
--Function that generates new column order index
create or replace function p_generate_order_index(
    --Table id
    a_table_id raw)
    return number
as
    a_order_index number(5);
begin
    select max(order_index) + 1 into a_order_index
    from meta_table_columns
    where table_id = a_table_id;
    
     if a_order_index is null then
        a_order_index := 0;
    end if;
    
    return a_order_index;
end;

--go

--Function of checking that column with the specified id and name doesn't exist
create or replace procedure p_check_col_not_exists(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the column id
    select count(*)
    into a_row_count
    from meta_table_columns
    where column_id = a_column_id;
    
    if a_row_count > 0 then
        raise_application_error(-20020,
                'A column with the specified id was already added (column_id = '
                || a_column_id
                || ') (#error_code = 20020)');
    end if;
    
    --Searching for the column name
    select count(*)
    into a_row_count
    from meta_table_columns
    where table_id = a_table_id and column_name = a_column_name;
    
    if a_row_count > 0 then
        raise_application_error(-20021,
                'A column with the specified name was already added (column_name = '
                || a_column_name
                || ') (#error_code = 20021)');
    end if;
end;

--go

--Function of adding string column
create or replace procedure meta_add_string_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_string_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values(a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);

    --Dropping table view
    p_drop_view_if_exists(a_table_name);

    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' nvarchar2(2000)';
        
    --Creating table view
    p_create_table_view(a_table_id);    
end;

--go

--Function of checking column existence
create or replace procedure p_check_col_exists(
    --Column id
    a_column_id raw)
as
    --Row count
    a_row_count number;
begin
    --Searching for the column
    select count(*)
    into a_row_count
    from meta_table_columns
    where column_id = a_column_id;
    
    if a_row_count = 0 then
        raise_application_error(-20019,
                'Specified column was not found (column_id = '
                || a_column_id
                || ') (#error_code = 20019)');
    end if;  
end;

--go

--Function of checking column type
create or replace procedure p_check_col_type(
    --Column id
    a_column_id raw,
    --Column table name
    a_column_table_name varchar2,
    --Column type name
    a_column_type_name varchar2)
as
    --Row count
    a_row_count number;
begin
    execute immediate 'select count(*) from '
    || a_column_table_name
    || ' where column_id = :1'
    into a_row_count
    using a_column_id;
    
    if a_row_count = 0 then
        raise_application_error(-20019,
                'Specified column (of type '
                || a_column_type_name
                || ') was not found (column_id = '
                || a_column_id
                || ') (#error_code = 20019)');
    end if;
end;

--go

--Function of removing string column
create or replace procedure meta_delete_column(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Column name
    a_column_name varchar(26);
    --Table name
    a_table_name varchar2(26);
    --Table column name
    a_column_table_name varchar2(30);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_exists(a_column_id);

    --Reading column properties
    select table_id, column_name, column_type, customization_level
    into a_table_id, a_column_name, a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --If this is ref column
    if a_column_table_name like '%meta_ref_columns' then
        --Dropping ref checking trigger
        p_drop_ref_checking(a_column_id);
    end if;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20005,
                'Deleting system column is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20005)');
    end if;   

    a_table_name := p_get_table_name(a_table_id);
    
    --Deleting column indexes (if any)
    p_delete_column_indexes(a_column_id);
    
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
    
     execute immediate 'alter table tbl_'
    || a_table_name
    || ' drop column ' || a_column_name
    || ' cascade constraints';
    
    --Updating the metadata
    execute immediate 'delete from ' || a_column_table_name
    || ' where column_id = :1'
    using a_column_id;
    
    --Creating table view
    p_create_table_view(a_table_id);
end;

--go

--Function of renaming string column
create or replace procedure meta_rename_column(
    --Column id
    a_column_id raw,
    --New column name
    a_new_column_name varchar2)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Old column name
    a_old_column_name varchar2(26);
    --Column renaming feature
    column_renaming_is_enabled number(1) := 0;
    --Column table name
    a_column_table_name varchar2(30);
    --Customization level
    a_customization_level number(10);
    --Row count
    a_row_count number;
begin
    p_check_col_exists(a_column_id);
    
    --Reading column properties
    select table_id, column_name, column_type, customization_level
    into a_table_id, a_old_column_name, a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --Searching for the column with duplicate name
    select count(*) into a_row_count
    from meta_table_columns
    where table_id = a_table_id and column_name = a_new_column_name;
    
    if a_row_count > 0 then
        raise_application_error(-20021,
                'A column with the specified name was already added (column_name = '
                || a_new_column_name
                || ') (#error_code = 20021)');
    end if;

    --If there is no need fo renaming
    if a_old_column_name = a_new_column_name then
        return;
    end if;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20006,
                'Renaming system column is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20006)');
    end if; 
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Checking column renaming
    select is_enabled into column_renaming_is_enabled
    from meta_dbms_features
    where feature_name = 'COLUMN_RENAMING';
    
    --If feature is enabled
    if column_renaming_is_enabled = 1 then
        
        --Changing the metadata
        execute immediate 'update ' || a_column_table_name
        || ' set column_name = :1 where column_id = :2'
        using a_new_column_name, a_column_id;
        
        --Dropping table view
        p_drop_view_if_exists(a_table_name);
        
        --If this is ref column
        if a_column_table_name like '%meta_ref_columns' then
            --Dropping ref checking trigger
            p_drop_ref_checking(a_column_id);
        end if;
        
        execute immediate 'alter table tbl_'
        || a_table_name
        || ' rename column ' || a_old_column_name 
        || ' to ' || a_new_column_name;
        
        --If this is ref column
        if a_column_table_name like '%meta_ref_columns' then
            --Creating ref checking trigger
            p_create_ref_checking(a_column_id);
        end if;
        
        --Recreating column index triggers
        p_recreate_col_index_triggers(a_column_id);
        
        --Creating table view
        p_create_table_view(a_table_id);        
    else
        raise_application_error(-20007,
                'Renaming a column is not supported by the dbms (column_id = '
                || a_column_id
                || ') (#error_code = 20007)');
    end if;
end;

--go

--Function of setting column nullability
create or replace procedure meta_set_column_nullability(
    --Column id
    a_column_id raw,
    --Is not null
    a_is_not_null integer)
as
    --Table id
    a_table_id raw(16);
    --Backend table name
    a_column_table_name varchar2(30);
    --Column name
    a_column_name varchar2(26);
    --Old is_not_null
    a_old_is_not_null integer;
    --Table name
    a_table_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_exists(a_column_id);
    
    --Reading column table name
    select column_type, customization_level
    into a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing system column nullability is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    --Reading column properties
    execute immediate 'select table_id, is_not_null, column_name from '
    ||  a_column_table_name || ' where column_id = :1'
    into a_table_id, a_old_is_not_null, a_column_name
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    if a_is_not_null = a_old_is_not_null then
        return;
    end if;
    
    if a_is_not_null = 1 then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' not null)';
    else
         execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' null)';
    end if;
    
    --Dropping table view (otherwise, the view can be unvalid)
    p_drop_view_if_exists(a_table_name);
    
    --Creating table view
    p_create_table_view(a_table_id);
    
    --Changing the metadata
    execute immediate 'update ' || a_column_table_name
    || ' set is_not_null = :1 where column_id = :2'
    using a_is_not_null, a_column_id;
end;

--go

--Function of setting column default value
create or replace procedure meta_set_string_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value nvarchar2)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
    --SQL cursor
    c integer;
    r integer;
    str nvarchar2(32767);
begin
    p_check_col_type(a_column_id, 'meta_string_columns', 'string');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_string_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
         raise_application_error(-20008,
                'Changing string system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
     
     if a_default_value is not null then
        str := 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        str := 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)'; 
    end if;
    
    --execute immediate is not used because it doesn't support nvarchar2
    c := dbms_sql.open_cursor;
    dbms_sql.parse (c, str, dbms_sql.native);
    r := dbms_sql.execute (c);
    dbms_sql.close_cursor (c);
    
    --Updating the metadata
    update meta_string_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of setting string column max length
create or replace procedure meta_set_string_max_length(
    --Column id
    a_column_id raw,
    --Max length
    a_max_length integer)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_string_columns', 'string');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_string_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing string system column max length is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;

    a_table_name := p_get_table_name(a_table_id);

    if a_max_length is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' nvarchar2(' || a_max_length || '))';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' nvarchar2(2000))';
    end if;
    
    --Updating the metadata
    update meta_string_columns
    set max_length = a_max_length
    where column_id = a_column_id;
end;

--go

--3--------------------------------------------------------------------------------------------
--Function of checking reference value
create or replace procedure p_check_reference_value(
    --Column id
    a_column_id raw,
    --Reference value
    a_ref_value raw)
as
    --Ref table name
    a_ref_table_name varchar2(26);
    --Row count
    row_count number;
begin     
     --If value is null there is no need for any check
     if a_ref_value is null then
        return;
     end if;
     
     --Enumerating all tables that the column references 
     for val in (select ref_table_id from meta_ref_targets
         where column_id = a_column_id) loop
         
         --Reading ref table name
         select table_name into a_ref_table_name
         from meta_tables
         where table_id = val.ref_table_id;
         
         --Searching for referenced row
         execute immediate 'select count(*) from tbl_'
         || a_ref_table_name
         || ' where id=:1'
         into row_count
         using a_ref_value;
         
         --If referenced row was found
         if row_count > 0 then
            return;
         end if;         
     end loop;
     
     --Referenced row was not found
     raise_application_error(-20024,
                'A row that was referenced by the column was not found (column_id = '
                || a_column_id
                || ', ref_value = '
                || a_ref_value
                || ') (#error_code = 20024)');
end;

--go

--Function of creating check ref value trigger
create or replace procedure p_create_ref_checking(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Table name
    a_table_name varchar2(26);
    --Trigger name
    a_trigger_name varchar2(30);
begin
    --Reading column properties
    select column_name, table_id
    into a_column_name, a_table_id
    from meta_ref_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
 
    --Creating trigger of checking column value
    a_trigger_name := 'r' || substr(a_column_id, 1, 29);
    
    execute immediate 'create or replace trigger ' || a_trigger_name
    || ' after insert or update of ' || a_column_name
    || ' on tbl_' || a_table_name
    || ' for each row'
    || ' declare pragma autonomous_transaction;'
    || ' begin'
    || ' p_check_reference_value('''
    || a_column_id || ''', :new.'
    || a_column_name || '); end;';
    
end;

--go

--Function of dropping check ref value trigger
create or replace procedure p_drop_ref_checking(
    --Column id
    a_column_id raw)
as
begin    
    execute immediate 'drop trigger ' || 'r' || substr(a_column_id, 1, 29);
end;

--go


--Function of adding reference column
create or replace procedure meta_add_ref_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
     a_table_name :=  p_get_table_name(a_table_id);
     
    --Changing the metadata
    insert into meta_ref_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);        
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --Creating new column
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' raw(16)';
         
    --Creating trigger of checking column value
    p_create_ref_checking(a_column_id);
         
    --Creating table view
    p_create_table_view(a_table_id);
end;

--go

--Function of setting column default value
create or replace procedure meta_set_ref_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_ref_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_ref_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of adding ref target
create or replace procedure meta_add_ref_target(
    --Column id
    a_column_id raw,
    --Ref table id
    a_ref_table_id raw)
as
    --Row count
    a_row_count number;
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
    
    select count(*) into a_row_count
    from meta_ref_targets
    where column_id = a_column_id and ref_table_id = a_ref_table_id;
    
    if a_row_count > 0 then
        raise_application_error(-20022,
                'Adding duplicate reference target is forbidden (column_id = '
                || a_column_id
                || ', ref_table_id = '
                || a_ref_table_id
                || ') (#error_code = 20022)');
    end if;
     
    insert into meta_ref_targets (column_id, ref_table_id)
    values (a_column_id, a_ref_table_id);
end;

--go

--Function of removing ref target
create or replace procedure meta_delete_ref_target(
    --Column id
    a_column_id raw,
    --Ref table id
    a_ref_table_id raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Ref column name
    a_ref_column_name varchar2(26);
    --Ref table name
    a_ref_table_name varchar2(26);
    --Row count
    a_row_count number;
begin
    --Searching for the target
    select count(*) 
    into a_row_count
    from meta_ref_targets
    where column_id = a_column_id
    and ref_table_id = a_ref_table_id;
    
    if a_row_count = 0 then
        raise_application_error(-20023,
                'Deleting nonexistent reference target is forbidden (column_id = '
                || a_column_id
                || ', ref_table_id = '
                || a_ref_table_id
                || ') (#error_code = 20023)');
    end if;

    --Reading ref column properties
    select column_name, table_id
    into a_ref_column_name, a_table_id
    from meta_ref_columns
    where column_id = a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    a_ref_table_name := p_get_table_name(a_ref_table_id);    

    execute immediate 'update tbl_'
    || a_table_name || ' parent'
    || ' set ' || a_ref_column_name || ' = null'
    || ' where exists('
    || 'select id from tbl_' || a_ref_table_name
    || ' where id = parent.' || a_ref_column_name || ')';

    delete from meta_ref_targets
    where column_id = a_column_id
    and ref_table_id = a_ref_table_id;
    
end;

--go

-- Function of setting the need to check reference values
create or replace procedure meta_set_ref_checking(
    --Column id
    a_column_id raw,
    --The need to check reference values
    a_ref_checking number)
as
    --Trigger name
    a_trigger_name varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
     
    --Reference checking trigger name
    a_trigger_name := 'r' || substr(a_column_id, 1, 29);
    
    if a_ref_checking = 1 then
        execute immediate 'alter trigger ' || a_trigger_name || ' enable';
    else
        execute immediate 'alter trigger ' || a_trigger_name || ' disable';
    end if;
    
    --Updating the metadata
    update meta_ref_columns
    set ref_checking = a_ref_checking 
    where column_id = a_column_id;
end;

--go

--4--------------------------------------------------------------------------------------------
--Function of adding enum values constraint
create or replace procedure p_create_enum_constraint(
       --Column id
       a_column_id raw)
as
    --Table id
    a_table_id raw(16);    
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Enum value list
    a_enum_value_list varchar2(32767) := '';
begin
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --Searching enum values
    for val in (select value from meta_enum_members
     where column_id = a_column_id) loop
             
        if trim(a_enum_value_list) is not null then
            a_enum_value_list := a_enum_value_list || ',';
        end if;
                     
        a_enum_value_list := a_enum_value_list || val.value;
             
    end loop;
         
    if trim(a_enum_value_list) is not null then     
        --Adding enum column constraint
        execute immediate 'alter table tbl_' || a_table_name
        || ' add constraint m' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || '= any(' || a_enum_value_list || '))';
    else
         --(so, one should specify enum members before inserting)
         execute immediate 'alter table tbl_' || a_table_name
         || ' add constraint m' || substr(a_column_id, 1, 29)
         || ' check(' || a_column_name || ' is null)';
    end if;
end;

--go

--Function af dropping enum values constraint
create or replace procedure p_drop_enum_constraint(
       --Column id
       a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
begin

    --Reading column properties
    select table_id into a_table_id
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
     
    execute immediate 'alter table tbl_' || a_table_name
    || ' drop constraint m' || substr(a_column_id, 1, 29);
     
end;

--go

--Function of creating enum column
create or replace procedure meta_add_enum_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_enum_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);

    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
   execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number(10)';
         
    --Creating enum values constraint
    p_create_enum_constraint(a_column_id);
         
    --Creating table view
    p_create_table_view(a_table_id);
    
end;

--go

--Function of adding new enum value
create or replace procedure meta_add_enum_member(
       --Column id
       a_column_id raw,
       --Value
       a_value number)
as
    --Row count
    a_row_count number;
begin
    
    --Searching for enum member
    select count(*)
    into a_row_count
    from meta_enum_members
    where column_id = a_column_id and value = a_value;
    
    if a_row_count <> 0 then
        raise_application_error(-20054,
                'A member with the specified value was already added (column_id = '
                || a_column_id || ', value = ' || a_value
                || ') (#error_code = 20054)');
    end if;

    --Changing the metadata
    insert into meta_enum_members (column_id, value)
    values(a_column_id, a_value);

    --Dropping enum constraint
    p_drop_enum_constraint(a_column_id);
         
    --Creating new enum constraint
    p_create_enum_constraint(a_column_id);

end;

--go

--Function of removing enum value
create or replace procedure meta_delete_enum_member(
    --Column id
    a_column_id raw,
    --Value
    a_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value
    a_default_value number(10);
    --Row count
    a_row_count number;
begin
    
    --Searching for enum member
    select count(*)
    into a_row_count
    from meta_enum_members
    where column_id = a_column_id and value = a_value;
    
    if a_row_count = 0 then
        raise_application_error(-20018,
                'Deleting nonexistent enumeration member is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20018)');
    end if;

    --Reading column properties
    select table_id, column_name, default_value
    into a_table_id, a_column_name, a_default_value
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If default value was deleted
     if a_value = a_default_value then
        --Setting default value to null
        meta_set_enum_default(a_column_id, null);
     end if;
    
     --Dropping enum constraint
     p_drop_enum_constraint(a_column_id);
             
     --Setting deleted member values to null
     execute immediate 'update tbl_' || a_table_name
     || ' set ' || a_column_name
     || ' = null where ' || a_column_name || ' = :1'
     using a_value;
     
     --Creating new enum constraint
     p_create_enum_constraint(a_column_id);
     
     --Changing the metadata
    delete from meta_enum_members
    where column_id = a_column_id
    and value = a_value;           
end;

--go

--Function of changing enum member value
create or replace procedure meta_set_enum_member_value(
    --Column name
    a_column_id raw,
    --Old value
    a_old_value number,
    --New value
    a_new_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value
    a_default_value number(10);
begin

    if a_new_value = a_old_value then
        return;
    end if;
        
    --Reading column properties
    select table_id, column_name, default_value
    into a_table_id, a_column_name, a_default_value
    from meta_enum_columns
    where column_id = a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    
    --If default value is equal to old value
    if a_default_value = a_old_value then
        --Setting default value to null
        meta_set_enum_default(a_column_id, null);
    end if;
        
    --Dropping old enum constraint
    p_drop_enum_constraint(a_column_id);
                 
    --Updating old member values
    execute immediate 'update tbl_' || a_table_name
    || ' set ' || a_column_name
    || ' = :1 where ' || a_column_name || ' = :2'
    using a_new_value, a_old_value;
    
    --Changing the metadata
    update meta_enum_members
    set value = a_new_value
    where column_id = a_column_id and value = a_old_value;
         
    --Creating new enum constraint
    p_create_enum_constraint(a_column_id);
    
    --If default value is equal to old value
    if a_default_value = a_old_value then
        --Setting default value to new value
        meta_set_enum_default(a_column_id, a_new_value);
    end if;
     
end;

--go

--Function of setting enum column default value
create or replace procedure meta_set_enum_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_enum_columns', 'enumeration');
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_enum_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of enabling/disabling enum value checking
create or replace procedure meta_set_enum_value_checking(
    --The column id
    a_column_id raw,
    --The need for enum value checking
    a_value_checking number)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_enum_columns', 'enumeration');
    
    --Reading the table name
    select table_name into a_table_name
    from meta_table_columns
    where column_id = a_column_id;
    
    --If value checking is enabled
    if a_value_checking = 1 then
        execute immediate 'alter table tbl_' || a_table_name
        || ' enable constraint m' || substr(a_column_id, 1, 29);
    --If value checking is disabled
    else
         execute immediate 'alter table tbl_' || a_table_name
        || ' disable constraint m' || substr(a_column_id, 1, 29);
    end if;
    
    --Updating the metadata
    update meta_enum_columns
    set value_checking = a_value_checking
    where column_id = a_column_id;
end;

--go

--5--------------------------------------------------------------------------------------------
--Function of adding decimal column
create or replace procedure meta_add_decimal_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_decimal_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);     

    --Dropping table view
    p_drop_view_if_exists(a_table_name);
     
     execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number';

    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting decimal column minimum
create or replace procedure meta_set_decimal_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum number;
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_minimum(a_column_id, a_minimum);
end;

--go

--Function of setting numeric column minimum
create or replace procedure p_set_numeric_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum number;
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;
    
    --Reading column properties
    execute immediate 'select table_id, column_name, minimum'
    || ' from ' || a_column_type
    || ' where column_id = :1'    
    into a_table_id, a_column_name, a_old_minimum
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
     
     --There is no need to drop table view
     
     if a_old_minimum is not null then
         --Drop old constraint 
         execute immediate 'alter table tbl_' 
         || a_table_name
         || ' drop constraint n' || substr(a_column_id, 1, 29);
     end if;
     
     --Create new check
     if a_minimum is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint n' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' >= ' || to_char(a_minimum,  '99999999999999999999999999999.999999') || ')';
     end if;
     
     --Updating the metadata
     if a_minimum is not null then
        execute immediate 'update ' || a_column_type
        || ' set minimum = :1 where column_id = :2'
        using a_minimum, a_column_id;
    else
         execute immediate 'update ' || a_column_type
        || ' set minimum = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go

--Function of setting decimal column maximum
create or replace procedure meta_set_decimal_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum number;
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_maximum(a_column_id, a_maximum);
end;

--go

--Function of setting numeric column maximum
create or replace procedure p_set_numeric_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum number;
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;

    --Reading column properties
    execute immediate 'select table_id, column_name, maximum'
    || ' from ' || a_column_type
    || ' where column_id = :1'    
    into a_table_id, a_column_name, a_old_maximum
    using a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    
    --There is no need to drop table view
         
    if a_old_maximum is not null then
        --Drop old constraint 
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint x' || substr(a_column_id, 1, 29);
    end if;
         
    --Create new check
    if a_maximum is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint x' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' <= ' || to_char(a_maximum,  '99999999999999999999999999999.999999') || ')';
    end if;
    
    --Updating the metadata
    if a_maximum is not null then
        execute immediate 'update ' || a_column_type
        || ' set maximum = :1 where column_id = :2'
        using a_maximum, a_column_id;
    else
         execute immediate 'update ' || a_column_type
        || ' set maximum = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go

--Function of setting decimal column precision
create or replace procedure meta_set_decimal_precision(
    --Column id
    a_column_id raw,
    --Precision
    a_precision number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old precision
    a_old_precision number(3);
    --Old scale
    a_old_scale number(3);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    --Reading column properties
    select table_id, column_name, precision, scale
    into a_table_id, a_column_name, a_old_precision, a_old_scale
    from meta_decimal_columns
    where column_id = a_column_id;
    
    --If there is no need for change
    if (a_precision = a_old_precision) 
        or (a_precision is null and a_old_precision is null) then
        return;
    end if;
         
    a_table_name := p_get_table_name(a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set precision = a_precision
    where column_id = a_column_id;
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --If precision is not specified
    if a_precision is null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric)';
    --If scale is not specified
    elsif a_old_scale is null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_precision || '))';
    --If precision and scale are specified
    elsif a_old_scale is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_precision || ',' || a_old_scale || '))';
    end if;
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting decimal column scale
create or replace procedure meta_set_decimal_scale(
    --Column id
    a_column_id raw,
    --Scale
    a_scale number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old precision
    a_old_precision number(3);
    --Old scale
    a_old_scale number(3);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    --Reading column properties
    select table_id, column_name, precision, scale
    into a_table_id, a_column_name, a_old_precision, a_old_scale
    from meta_decimal_columns
    where column_id = a_column_id;
    
    --If there is no need for change
    if (a_scale = a_old_scale) 
        or (a_scale is null and a_old_scale is null) then
        return;
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set scale = a_scale
    where column_id = a_column_id;
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --If scale is not specified
    if (a_old_precision is not null) and (a_scale is null) then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_old_precision || '))';
    --If precision and scale are specified
    elsif  (a_old_precision is not null) and (a_scale is not null) then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_old_precision || ',' || a_scale || '))';
    end if;
         
    --Creating table view
    p_create_table_view(a_table_id);
    
end;

--go

--Function of setting column default value
create or replace procedure meta_set_decimal_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_default(a_column_id, a_default_value);
end;

--go

--Function of setting numeric default value
create or replace procedure p_set_numeric_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;
    
    --Reading column properties
    execute immediate 'select table_id, column_name'
    || ' from ' || a_column_type
    || ' where column_id = :1'
    into a_table_id, a_column_name
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ' || to_char(a_default_value,  '99999999999999999999999999999.999999') || ')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    if a_default_value is not null then
        execute immediate 'update ' || a_column_type
        || ' set default_value = :1 where column_id = :2'
        using a_default_value, a_column_id;
    else
        execute immediate 'update ' || a_column_type
        || ' set default_value = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go

--6--------------------------------------------------------------------------------------------
--Function of adding new datetime column
create or replace procedure meta_add_datetime_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_datetime_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' date';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting datetime column minimum
create or replace procedure meta_set_datetime_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum date;
    --Minimum str
    a_minimum_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, minimum, column_name, customization_level
    into a_table_id, a_old_minimum, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column minimum is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If there is no need for change
    if (a_minimum = a_old_minimum) or
        (a_minimum is null and a_old_minimum is null) then
        return;
    end if;
         
    --There is no need to drop table view
    
    if a_old_minimum is not null then
        --Drop old constraint
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint n' || substr(a_column_id, 1, 29);
    end if;         
    
    if a_minimum is not null then
        a_minimum_str := to_char(a_minimum, 'DD-MM-YYYY HH24:MI:SS');
        
        --Create new check
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint n' ||  substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' >= to_date(''' || a_minimum_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set minimum = a_minimum
    where column_id = a_column_id;
end;

--go

--Function of setting datetime column maximum
create or replace procedure meta_set_datetime_maximum(
    --Column id
    a_column_id raw,
    --Minimum
    a_maximum date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum date;
    --Maximum str
    a_maximum_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, maximum, column_name, customization_level
    into a_table_id, a_old_maximum, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column maximum is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If there is no need for change
    if (a_maximum = a_old_maximum) or
        (a_maximum is null and a_old_maximum is null) then
        return;
    end if;
         
    --There is no need to drop table view
    
    if a_old_maximum is not null then
        --Drop old constraint
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint x' || substr(a_column_id, 1, 29);
    end if;         
    
    if a_maximum is not null then
        a_maximum_str := to_char(a_maximum, 'DD-MM-YYYY HH24:MI:SS');
        
        --Create new check
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint x' ||  substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' <= to_date(''' || a_maximum_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set maximum = a_maximum
    where column_id = a_column_id;
end;

--go

--Function of setting column default value
create or replace procedure meta_set_datetime_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value str
    a_default_value_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        a_default_value_str := to_char(a_default_value, 'DD-MM-YYYY HH24:MI:SS');
                
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default to_date(''' || a_default_value_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--7--------------------------------------------------------------------------------------------
--Function of creating boolean column
create or replace procedure meta_add_bool_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
         
    a_table_name :=  p_get_table_name(a_table_id);
         
    --Changing the metadata
    insert into meta_bool_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
    p_generate_order_index(a_table_id), 1);
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add  ' || a_column_name
    || ' number(1)';
    
    --Creating value constraint
    execute immediate 'alter table tbl_'
    || a_table_name
    || ' add constraint b' || substr(a_column_id, 1, 29)
    || ' check(' || a_column_name || '=0 or ' 
    || a_column_name || '=1)';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting column default value
create or replace procedure meta_set_bool_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_bool_columns', 'bool');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_bool_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing bool system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ' || a_default_value || ')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_bool_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--8--------------------------------------------------------------------------------------------
--Function of creating binary column
create or replace procedure meta_add_binary_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
         
    a_table_name :=  p_get_table_name(a_table_id);
         
    --Changing the metadata
    insert into meta_binary_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
    p_generate_order_index(a_table_id), 1);
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add  ' || a_column_name
    || ' blob';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--9--------------------------------------------------------------------------------------------
--Function of adding new integer column
create or replace procedure meta_add_int_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number(10)';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting column default value
create or replace procedure meta_set_int_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_int_columns', 'int');
    
    p_set_numeric_default(a_column_id, a_default_value);
end;

--go

--Function of setting int column maximum
create or replace procedure meta_set_int_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
begin
    p_check_col_type(a_column_id, 'meta_int_columns', 'int');
    
    p_set_numeric_maximum(a_column_id, a_maximum);
end;

--go

--Function of setting int column minimum
create or replace procedure meta_set_int_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
begin
    p_check_col_type(a_column_id, 'meta_int_columns', 'int');
    
    p_set_numeric_minimum(a_column_id, a_minimum);
end;

--go

--10-------------------------------------------------------------------------------------------
--Function of adding new big integer column
create or replace procedure meta_add_big_int_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_big_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number(20)';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting column default value
create or replace procedure meta_set_big_int_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
begin
    p_check_col_type(a_column_id, 'meta_big_int_columns', 'big int');
    
    p_set_numeric_default(a_column_id, a_default_value);
end;

--go

--Function of setting big int column maximum
create or replace procedure meta_set_big_int_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
begin
    p_check_col_type(a_column_id, 'meta_big_int_columns', 'big int');
    
    p_set_numeric_maximum(a_column_id, a_maximum);
end;

--go

--Function of setting big int column minimum
create or replace procedure meta_set_big_int_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
begin
    p_check_col_type(a_column_id, 'meta_big_int_columns', 'big int');
    
    p_set_numeric_minimum(a_column_id, a_minimum);
end;

--go

--11-------------------------------------------------------------------------------------------
--Function of adding new small integer column
create or replace procedure meta_add_small_int_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_small_int_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number(5)';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting column default value
create or replace procedure meta_set_small_int_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
begin
    p_check_col_type(a_column_id, 'meta_small_int_columns', 'small int');
    
    p_set_numeric_default(a_column_id, a_default_value);
end;

--go

--Function of setting small int column maximum
create or replace procedure meta_set_small_int_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
begin
    p_check_col_type(a_column_id, 'meta_small_int_columns', 'small int');
    
    p_set_numeric_maximum(a_column_id, a_maximum);
end;

--go

--Function of setting small int column minimum
create or replace procedure meta_set_small_int_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
begin
    p_check_col_type(a_column_id, 'meta_small_int_columns', 'small int');
    
    p_set_numeric_minimum(a_column_id, a_minimum);
end;

--go

--12-------------------------------------------------------------------------------------------
--Function of adding new guid column
create or replace procedure meta_add_guid_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_guid_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' raw(16)';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting column default value
create or replace procedure meta_set_guid_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_guid_columns', 'guid');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_guid_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing guid system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_guid_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--14-------------------------------------------------------------------------------------------
--Function of adding new text column
create or replace procedure meta_add_text_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_text_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' nclob';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--50------------------------------------------------------------------------------------------
--Function of adding new index
create or replace procedure meta_add_index(
    --Index id
    a_index_id raw,
    --Table id
    a_table_id raw,
    --Is unique
    a_is_unique number,
    --Column id list
    a_column_id_list raw)
as
    --Table name
    a_table_name varchar2(26);
    --Column list
    a_column_list varchar2(32767) := '';
    --Column id
    a_column_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Current index
    a_curr_index number := 1;
    --Current position
    a_curr_position number := 1;
    --Item length
    a_item_length number := 16;
    --Row count
    a_row_count number;
begin

    --If there is no column specified
    if a_column_id_list is null then
        return;
    end if;

    --Searching for the index with the same id
    select count(*) into a_row_count
    from meta_indexes
    where index_id = a_index_id;
    
    if a_row_count > 0 then
        raise_application_error(-20028,
                'A index with the specified id was already added (index_id = '
                || a_index_id
                || ') (#error_code = 20028)');
    end if;
    
    --Searching for the index with the same column set
    select count(*) into a_row_count
    from meta_indexes
    where column_id_list = a_column_id_list;
    
     if a_row_count > 0 then
        raise_application_error(-20029,
                'A index with the specified column set was already added (#error_code = 20029)');
    end if;
    
    --Reading table name
    a_table_name := p_get_table_name(a_table_id);
    
    --Reading user columns
    loop
        exit when dbms_lob.getlength(a_column_id_list) - a_curr_position + 1 < 16;
        
        dbms_lob.read(a_column_id_list, a_item_length, a_curr_position, a_column_id);
        
        a_curr_index := a_curr_index + 1;
        a_curr_position := a_curr_position + 16;
            
        --Reading column name
        select column_name into a_column_name
        from meta_table_columns
        where table_id = a_table_id
        and column_id = a_column_id;
            
        if length(a_column_list) > 0 then
            a_column_list := a_column_list || ',';
        end if;
            
        a_column_list := a_column_list || a_column_name;
    end loop;
    
    --Creating the index
    execute immediate 'create index i'
    || substr(a_index_id, 1, 29)
    || ' on tbl_'  || a_table_name
    || ' (' || a_column_list || ')';
    
    --Updating the metadata
    insert into meta_indexes(index_id, table_id, is_unique, column_id_list)
    values (a_index_id, a_table_id, a_is_unique, a_column_id_list);
    
    --If the index is unique
    if a_is_unique = 1 then
        p_recreate_index_triggers(a_index_id);
    end if;
end;

--go

--Function that recreates the index triggers
create or replace procedure p_recreate_index_triggers(
    --Index id
    a_index_id raw)
as
    --Table id
    a_table_id raw(16);
    --Is unique
    a_is_unique number;
    --Trigger column condition
    a_column_condition varchar2(32767) := '';
    --Column list
    a_column_list varchar2(32767) := 'is_active';
    --Table name
    a_table_name varchar2(26);
    --Column id list
    a_column_id_list raw(8000);
    --Column id
    a_column_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Current index
    a_curr_index number := 1;
    --Current position
    a_curr_position number := 1;
    --Item length
    a_item_length number := 16;
begin
    --Reading index properties
    select table_id, is_unique, column_id_list into a_table_id, a_is_unique, a_column_id_list
    from meta_indexes
    where index_id = a_index_id;
    
    --Reading table name
    a_table_name := p_get_table_name(a_table_id);
    
    --If the index is unique
    if a_is_unique = 1 then
        --Reading user columns
        loop
            exit when dbms_lob.getlength(a_column_id_list) - a_curr_position + 1 < 16;
            
            dbms_lob.read(a_column_id_list, a_item_length, a_curr_position, a_column_id);
            
            a_curr_index := a_curr_index + 1;
            a_curr_position := a_curr_position + 16;
                
            --Reading column name
            select column_name into a_column_name
            from meta_table_columns
            where table_id = a_table_id
            and column_id = a_column_id;
            
            --If it is not the is_active column which is handled automatically
            if a_column_name <> 'is_active' then               
                a_column_list := a_column_list || ','  || a_column_name;
            end if;
            
            if length(a_column_condition) > 0 then  
                a_column_condition := a_column_condition || ' and ';
            end if;

            a_column_condition := a_column_condition || 's.' 
            || a_column_name || '=t.' || a_column_name;
        end loop;
    
        --Creating the trigger that registers modified rows
        execute immediate 'create or replace trigger z' || substr(a_index_id, 1, 29)
        || ' after insert or update of ' || a_column_list
        || ' on tbl_' || a_table_name || ' for each row'
        || ' declare'
        || ' begin'
        || '   insert into meta_modified_rows(table_id, row_id) values(''' || a_table_id || ''', :new.id);'
        || ' end;';
        
        --Creating the trigger that enforces uniqueness
        execute immediate 'create or replace trigger q' || substr(a_index_id, 1, 29)
        || ' after insert or update of ' || a_column_list
        || ' on tbl_' || a_table_name
        || ' declare'
        || ' a_recycle_bin number;'
        || ' a_row_count number;'
        || ' begin'
            --Checking the recycle bin availability
        || '   select int_value into a_recycle_bin from meta_options where option_name = ''RECYCLE_BIN'';'
        || '   for item in (select row_id from meta_modified_rows where table_id = hextoraw(''' || a_table_id || ''')) loop'
                  --If the recycle bin is enabled
        || '     if a_recycle_bin = 1 then'
                    --Searching for another row with the same column values in active rows
        || '        select count(*) into a_row_count from tbl_' || a_table_name || ' s'
        || '        inner join tbl_' || a_table_name || ' t on t.id=item.row_id'
        || '        where s.is_active = 1 and ' || a_column_condition || ';'
            --If the recycle bin is not enabled
        || '     else'
                  --Searching for another row with the same column values in all rows
        || '        select count(*) into a_row_count from tbl_' || a_table_name || ' s'
        || '        inner join tbl_' || a_table_name || ' t on t.id=item.row_id'
        || '        where ' || a_column_condition || ';'
        || '     end if;'
        || '     if a_row_count > 1 then'
        || '        raise_application_error(-20055,'
        || '          ''The operation results in breaking the column values uniqueness (id = '' || item.row_id || '') (#error_code = 20055)'');'
        || '     end if;'
        || '   end loop;'
        || ' end;';
    end if;
end;

--go

--Function that recreates the column index triggers
create or replace procedure p_recreate_col_index_triggers(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
begin
    --Reading column properties
    select table_id
    into a_table_id
    from meta_table_columns
    where column_id = a_column_id;
    
    for i in (select index_id, column_id_list from meta_indexes
        where table_id = a_table_id and is_unique = 1) loop
        
        if p_column_is_indexed(i.index_id, a_column_id) = 1 then
           p_recreate_index_triggers(i.index_id);
        end if;
        
    end loop;
end;

--go

--Function of checking if column is indexed
create or replace function p_column_is_indexed(
    --Index id
    a_index_id raw,
    --Column id
    a_column_id raw)
return number
as
    --Index column id list
    a_column_id_list blob;
    --Position
    a_position number;
begin
    --Reading column id list
    select column_id_list
    into a_column_id_list
    from meta_indexes
    where index_id = a_index_id;
    
    a_position := dbms_lob.instr(a_column_id_list, a_column_id);
    
    if a_position = 0 then
        return 0;
    elsif (a_position - 1) mod 16 = 0 then
        return 1;
    else
        return 0;
    end if;
end;

--go

--Function of deleting all indexes that include the column
create or replace procedure p_delete_column_indexes(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
begin
    --Reading column properties
    select table_id
    into a_table_id
    from meta_table_columns
    where column_id = a_column_id;
    
    for i in (select index_id, column_id_list from meta_indexes
        where table_id = a_table_id) loop
        
        if p_column_is_indexed(i.index_id, a_column_id) = 1 then
           meta_delete_index(i.index_id);
        end if;
        
    end loop;
end;

--go

--Function of dropping table index
create or replace procedure meta_delete_index(
    --Index id
    a_index_id raw)
as
    --Row count
    a_row_count number;
    --Is unique
    a_is_unique number;
begin
    --Searching for the index with the same id
    select count(*) into a_row_count
    from meta_indexes
    where index_id = a_index_id;
    
    if a_row_count = 0 then
        raise_application_error(-20030,
                'A index with the specified id was not found (index_id = '
                || a_index_id
                || ') (#error_code = 20030)');
    end if;
    
    --Checking if the index is unique
    select is_unique into a_is_unique
    from meta_indexes
    where index_id = a_index_id;
    
    --Dropping the index
    execute immediate 'drop index i'
    || substr(a_index_id, 1, 29);
    
    --If the index is unique
    if a_is_unique = 1 then
        --Dropping the triggers
        execute immediate 'drop trigger q' || substr(a_index_id, 1, 29);
        execute immediate 'drop trigger z' || substr(a_index_id, 1, 29);
    end if;
    
    --Updating the metadata
    delete from meta_indexes
    where index_id = a_index_id;
end;

--go

--100-----------------------------------------------------------------------------------------
--Function of adding a business unit
create or replace procedure meta_add_unit(
    --Unit id
    a_unit_id raw,
    --Unit name
    a_unit_name nvarchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the unit with the same id
    select count(*) into a_row_count
    from meta_units
    where unit_id = a_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20031,
                'A unit with the specified id was already added (unit_id = '
                || a_unit_id
                || ') (#error_code = 20031)');
    end if;
    
    --Searching for the unit with the same name
     select count(*) into a_row_count
    from meta_units
    where unit_name = a_unit_name;
    
    if a_row_count > 0 then
        raise_application_error(-20017,
                'A unit with the specified name was already added (unit_name = '
                || a_unit_name
                || ') (#error_code = 20017)');
    end if;

    --Updating the metadata
    insert into meta_units(unit_id, unit_name)
    values(a_unit_id, a_unit_name);
end;

--go

--Function of checking unit existence
create or replace procedure p_check_unit_exists(
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number := 0;
begin
    --Searching for the unit
    select count(*) into a_row_count
    from meta_units
    where unit_id = a_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20032,
                'A unit with the specified id was not found (unit_id = '
                || a_unit_id
                || ') (#error_code = 20032)');
    end if;
end;

--go

--Function of deleting a business unit
create or replace procedure meta_delete_unit(
    --Unit id
    a_unit_id raw)
as
    --Affected unit users
    a_affected_users meta_name_list;
begin
    p_check_unit_exists(a_unit_id);
    
    --Getting affected old users
    select distinct user_name
    bulk collect into a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from table(p_get_unit_parents_children(a_unit_id)));
    
    --References to and from unit will be deleted on cascade
    
    --Deleting the unit
    delete from meta_units
    where unit_id = a_unit_id;
    
    --Refreshing old users acl
    for u in (select column_value user_name from table(a_affected_users)) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go

--Function of getting unit parents
create or replace function p_get_unit_parents(
    --Unit id
    a_unit_id raw)
return meta_id_list 
as
    --Parent units id list
    result meta_id_list := meta_id_list();
begin
     select distinct *
     bulk collect into result
     from (
         select parent_unit_id
         from meta_unit_parents
         start with unit_id = a_unit_id
         connect by prior parent_unit_id = unit_id
         union
         select a_unit_id from dual);

     return result;
end;

--go

--Function of getting unit children
create or replace function p_get_unit_children(
    --Unit id
    a_unit_id raw)
return meta_id_list
as
    --Child units id list
    result meta_id_list := meta_id_list();
begin
     select distinct *
     bulk collect into result
     from (
         select unit_id
         from meta_unit_parents
         start with parent_unit_id = a_unit_id
         connect by prior unit_id = parent_unit_id
         union
         select a_unit_id from dual);
     
     return result;
end;

--go

--Function of getting unit children and parents
create or replace function p_get_unit_parents_children(
    --Unit id
    a_unit_id raw)
return meta_id_list
as
    --Result units id list
    result meta_id_list := meta_id_list();
begin
     select distinct unit_id
     bulk collect into result
     from (     
        --The children
        select unit_id
        from meta_unit_parents
        start with parent_unit_id = a_unit_id
        connect by prior unit_id = parent_unit_id
        union
        --The parents
        select parent_unit_id
        from meta_unit_parents
        start with unit_id = a_unit_id
        connect by prior parent_unit_id = unit_id
        union
        select a_unit_id from dual);

     return result;
end;

--go

--Function of adding unit parent
create or replace procedure meta_add_unit_parent(
    --Unit id
    a_unit_id raw,
    --Parent unit id
    a_parent_unit_id raw)
as
    --Row count
    a_row_count number;
    --Unit id list
    a_unit_id_list meta_id_list;
begin
    p_check_unit_exists(a_unit_id);
    p_check_unit_exists(a_parent_unit_id);
    
    --Searching for the same unit parent
    select count(*) into a_row_count
    from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20050,
                'Specified parent unit was already added (unit_id = '
                || a_unit_id || ', parent_unit_id = '
                || a_parent_unit_id
                || ') (#error_code = 20050)');
    end if;
           
    --Checking that there will be no cycle
    a_unit_id_list := p_get_unit_parents(a_parent_unit_id);
    
    for i in 1..a_unit_id_list.count loop
        if a_unit_id_list(i) = a_unit_id then
            raise_application_error(-20033,
                'Building a cycle of units is forbidden (#error_code = 20033)');
        end if;            
    end loop;
    
    --Updating the metadata
    insert into meta_unit_parents (unit_id, parent_unit_id)
    values (a_unit_id, a_parent_unit_id);
    
    --Refreshing new users acl
    for u in (select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from table(p_get_unit_parents_children(a_unit_id)))) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go

--Function of deleting unit parent
create or replace procedure meta_delete_unit_parent(
    --Unit id
    a_unit_id raw,
    --Parent unit id
    a_parent_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected units
    a_affected_units meta_id_list := meta_id_list();
begin
    p_check_unit_exists(a_unit_id);
    p_check_unit_exists(a_parent_unit_id);
    
    --Searching for the unit parent
    select count(*) into a_row_count
    from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20051,
                'Specified parent unit was not found (unit_id = '
                || a_unit_id || ', parent_unit_id = '
                || a_parent_unit_id
                || ') (#error_code = 20051)');
    end if;
    
    --Getting affected old units
    if a_parent_unit_id is not null then
        a_affected_units := p_get_unit_parents_children(a_unit_id);
    end if;
        
    --Updating the metadata
    delete from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
   
    --Refreshing new users acl
    for u in (select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from table(a_affected_units))) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go

--101-----------------------------------------------------------------------------------------

--Function of adding a role
create or replace procedure meta_add_role(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_roles
    where role_name = lower(a_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20034,
                'A role with the specified name was already added (role_name = '
                || a_role_name
                || ') (#error_code = 20034)');
    end if;

    begin
        --Creating a role
        execute immediate 'create role ' || a_role_name;
    exception when others then
        --If a role with the specified name already exists
        raise_application_error(-20034,
                'A role with the specified name was already added (role_name = '
                || a_role_name
                || ') (#error_code = 20034)');
    end;

    --Updating the metadata
    insert into meta_roles (role_name)
    values(lower(a_role_name));    
end;

--go

--Function of checking role existence
create or replace procedure p_check_role_exists(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_roles
    where role_name = lower(a_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20035,
                'A role with the specified name was not found (role_name = '
                || a_role_name
                || ') (#error_code = 20035)');
    end if;
end;

--go

--Function of deleting a role
create or replace procedure meta_delete_role(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
    --Affected users
    a_affected_users meta_name_list;
begin
    p_check_role_exists(a_role_name);
    
    --Getting affected users
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
    --Deleting the role
    begin
        execute immediate 'drop role ' || a_role_name;
    exception when others then
        --Doing nothing (this is tolerable)
        select 1 into a_row_count from dual;
    end;
    
    --Deleting all role acl
    delete from meta_table_acl
    where principal_name = lower(a_role_name);
    
    --User enrollments and role enrollments
    -- will be deleted on cascade
    
    --Updating the metadata
    delete from meta_roles
    where role_name = lower(a_role_name);
    
    --Updating role users acl
    for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;    
end;

--go

--Function of adding user in role
create or replace procedure meta_add_user_in_role(
    --User name
    a_user_name varchar2,
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    p_check_role_exists(a_role_name);

    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_user_roles
    where user_name = lower(a_user_name) 
    and container_role_name = lower(a_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20037,
                'Adding a user in a role twice is forbidden (role_name = '
                || a_role_name || ', user_name = '
                || a_user_name
                || ') (#error_code = 20037)');
    end if;
    
    --Enrolling user in role
    execute immediate 'grant ' || a_role_name || ' to ' || a_user_name;
    
    --Updating the metadata
    insert into meta_user_roles (user_name, container_role_name)
    values(lower(a_user_name), lower(a_role_name));
    
    --Refreshing user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of removing user from role
create or replace procedure meta_delete_user_from_role(
    --User name
    a_user_name varchar2,
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    p_check_role_exists(a_role_name);
    
    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_user_roles
    where user_name = lower(a_user_name)
    and container_role_name = lower(a_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20039,
                'Specified user enrollment was not found (role_name = '
                || a_role_name || ', user_name = '
                || a_user_name
                || ') (#error_code = 20039)');
    end if;
    
    --Deleting user from role
    execute immediate 'revoke ' || a_role_name || ' from ' || a_user_name;

    --Updating the metadata
    delete from meta_user_roles
    where user_name = lower(a_user_name) 
    and container_role_name = lower(a_role_name);
    
    --Refreshing user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of adding role in role
create or replace procedure meta_add_role_in_role(
    --Role name
    a_role_name varchar2,
    --Container role name
    a_container_role_name varchar2)
as
    --Affected users
    a_affected_users meta_name_list := meta_name_list();
    --Row count
    a_row_count number;
begin
    p_check_role_exists(a_container_role_name);
    p_check_role_exists(a_role_name);
    
    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_role_roles
    where role_name = lower(a_role_name) 
    and container_role_name = lower(a_container_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20038,
                'Adding a role in a role twice is forbidden (container_role_name = '
                || a_container_role_name || ', role_name = '
                || a_role_name
                || ') (#error_code = 20038)');
    end if;
    
    --Adding role in role
    execute immediate 'grant ' || a_container_role_name
    || ' to ' || a_role_name;
    
    --Updating the metadata
    insert into meta_role_roles(role_name, container_role_name)
    values(lower(a_role_name), lower(a_container_role_name));
    
    --Updating users acl
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
     for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;
end;

--go

--Function of removing role from role
create or replace procedure meta_delete_role_from_role(
    --Role name
    a_role_name varchar2,
    --Container role name
    a_container_role_name varchar2)
as
    --Row count
    a_row_count number;
    --Affected users
    a_affected_users meta_name_list;
begin
    p_check_role_exists(a_container_role_name);
    p_check_role_exists(a_role_name);
    
    --Searching for role enrollment
    select count(*) into a_row_count
    from meta_role_roles
    where role_name = lower(a_role_name)
    and container_role_name = lower(a_container_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20040,
                'Specified role enrollment was not found (container_role_name = '
                || a_container_role_name || ', role_name = '
                || a_role_name
                || ') (#error_code = 20040)');
    end if;
    
    execute immediate 'revoke ' || a_container_role_name
    || ' from ' || a_role_name;
    
    --Updating the metadata
    delete from meta_role_roles
    where  role_name = lower(a_role_name)
    and container_role_name = lower(a_container_role_name);
    
    --Updating users acl
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
    for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;

end;

--go

--102-----------------------------------------------------------------------------------------
--Function of getting current user
create or replace procedure meta_get_current_user(
  a_current_user_name out varchar2)
as
begin
    select user into a_current_user_name from dual;
end;

--go

--Function of checking user existence
create or replace procedure p_check_user_exists(
    --User name
    a_user_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(a_user_name);
    
    if a_row_count = 0 then
         raise_application_error(-20036,
                'A user with the specified name was not found (user_name = '
                || a_user_name
                || ') (#error_code = 20036)');
    end if;
end;

--go

--Function of adding new user
create or replace procedure meta_add_user(
    --User name
    a_user_name varchar2,
    --User password
    a_user_password varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for a user with the same name
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(a_user_name);
    
    if a_row_count > 0 then
        raise_application_error(-20041,
                'A user with the specified name was already added (user_name = '
                || a_user_name
                || ') (#error_code = 20041)');
    end if;
    
    --Checking if a user with the specified name exists in the database
    select count(*) into a_row_count from all_users where username = upper(a_user_name);

    --If there is no user with the specified name
    if a_row_count = 0 then
        --If the user password was specified
        if a_user_password is not null and length(trim(a_user_password)) > 0 then
            --Creating a new user
            execute immediate 'create user ' || a_user_name
            || ' identified by "' || a_user_password || '"';
        --If the user password was not specified
        else
            --Creating a new user with the default password
            execute immediate 'create user ' || a_user_name
            || ' identified by "P@ssw0rd"';
        end if;        
        
    --If there is a user with the specified name and the new password was specified
    elsif a_user_password is not null and length(trim(a_user_password)) > 0 then
        --Altering the user password
        execute immediate 'alter user ' || a_user_name
        || ' identified by "' || a_user_password || '"';
    end if;
    
    --Granting default privileges
    
    --Read metadata version
    execute immediate 'grant select on meta_version to ' || a_user_name;
    
    --Read and write dbms features
    execute immediate 'grant select on meta_dbms_features to ' || a_user_name;
    execute immediate 'grant update on meta_dbms_features to ' || a_user_name;
    
    --Read schema metadata
    execute immediate 'grant select on meta_tables to ' || a_user_name;
    execute immediate 'grant select on meta_string_columns to ' || a_user_name;
    execute immediate 'grant select on meta_ref_columns to ' || a_user_name;
    execute immediate 'grant select on meta_ref_targets to ' || a_user_name;
    execute immediate 'grant select on meta_enum_columns to ' || a_user_name;
    execute immediate 'grant select on meta_enum_members to ' || a_user_name;
    execute immediate 'grant select on meta_big_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_binary_columns to ' || a_user_name;
    execute immediate 'grant select on meta_bool_columns to ' || a_user_name;
    execute immediate 'grant select on meta_datetime_columns to ' || a_user_name;
    execute immediate 'grant select on meta_decimal_columns to ' || a_user_name;
    execute immediate 'grant select on meta_guid_columns to ' || a_user_name;
    execute immediate 'grant select on meta_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_small_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_text_columns to ' || a_user_name;
    execute immediate 'grant select on meta_table_columns to ' || a_user_name;
    
    --Reading indexes
    execute immediate 'grant select on meta_indexes to ' || a_user_name;
    
    --Reading options
    execute immediate 'grant select on meta_options to ' || a_user_name;
    
    --Executing common functions
    execute immediate 'grant execute on meta_get_dbms_name to ' || a_user_name;
    execute immediate 'grant execute on meta_get_dbms_version to ' || a_user_name;
    execute immediate 'grant execute on meta_get_current_db_name to ' || a_user_name;
    execute immediate 'grant execute on meta_get_current_user to ' || a_user_name;
    execute immediate 'grant execute on meta_set_curr_user_password to ' || a_user_name;
    
    --Reading user names and display names
    execute immediate 'grant select on meta_user_names to ' || a_user_name;
    
    --Current user properties
    execute immediate 'grant select on meta_current_user to ' || a_user_name; 
    execute immediate 'grant update(display_name) on meta_current_user to ' || a_user_name; 
    
    --Read unit list
    execute immediate 'grant select on meta_units to ' || a_user_name;
    execute immediate 'grant select on meta_unit_parents to ' || a_user_name;
    execute immediate 'grant select on meta_user_units to ' || a_user_name;
    
    --Read user's acl unit-level access rights
    execute immediate 'grant select on meta_user_acl_unit_access to ' || a_user_name;
    --Read user's acl full access rights
    execute immediate 'grant select on meta_user_acl_full_access to ' || a_user_name;
    --Read user's acl own rows access rights
    execute immediate 'grant select on meta_user_acl_own_rows to ' || a_user_name;
    
    --Soft delete table rows
    execute immediate 'grant execute on meta_soft_delete_table_row to ' || a_user_name;
    
    --Calling main security function (security check is implemented inside this function)
    execute immediate 'grant execute on meta_set_user_change_security to ' || a_user_name;
    
    --Updating the metadata
    insert into meta_users(user_name)
    values(lower(a_user_name));
    
    --Granting login permission
    meta_set_user_login(a_user_name, 1);
end;

--go

--Function of allowing user login
create or replace procedure meta_set_user_login(
    --User name
    a_user_name varchar2,
    --Can login
    a_can_login number)
as
    --Old CanLogin
    a_old_can_login number(1);
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20042,
                'Changing current user login permission is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20042)');
    end if;

    --Reading old CanLogin
    select can_login
    into a_old_can_login
    from meta_users
    where user_name = lower(a_user_name);
    
    --if there is no need for change
    if a_can_login = a_old_can_login then
        return;
    end if;
    
    --Changing the metadata
    update meta_users
    set can_login = a_can_login
    where user_name = lower(a_user_name);
    
    --Granting or revoking the privilege
    if a_can_login = 1 then
        execute immediate 'grant create session to ' || a_user_name;
    else
        execute immediate 'revoke create session from ' || a_user_name;
    end if;

end;

--go

--Function of setting user CanChangeSchema
create or replace procedure meta_set_user_change_schema(
    --User name
    a_user_name varchar2,
    --Can change schema
    a_can_change_schema number)
as
    --Current user can change security
    a_user_can_change_security number(1);
    --Old CanChangeSchema
    a_old_can_change_schema number(1);
begin
    p_check_user_exists(a_user_name);
    
    --Reading old CanChangeSchema
    select can_change_schema
    into a_old_can_change_schema
    from meta_users
    where user_name = lower(a_user_name);
    
    --If there is no need for change
    if a_can_change_schema = a_old_can_change_schema then
        return;
    end if;
    
    if a_can_change_schema = 1 then
        p_enable_schema_change(a_user_name);
    else
        p_disable_schema_change(a_user_name); 
    end if;
    
    --Changing the metadata
    update meta_users
    set can_change_schema = a_can_change_schema
    where user_name = lower(a_user_name);
    
end;

--go

--Function of enabling schema change
create or replace procedure p_enable_schema_change(
    --User name
    a_user_name varchar2)
as
begin
    --Update metadata version
    execute immediate 'grant update on meta_version to ' || a_user_name;
    
    --Granting update on meta tables
    execute immediate 'grant update(display_name, description) on meta_tables to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_string_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_ref_columns to ' || a_user_name;
    execute immediate 'grant update(cascade_delete) on meta_ref_targets to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_enum_columns to ' || a_user_name;
    execute immediate 'grant update(display_name) on meta_enum_members to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_big_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_binary_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_bool_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_datetime_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_decimal_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_guid_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_small_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_text_columns to ' || a_user_name;
    
    --Grant execute on schema changing functions
    execute immediate 'grant execute on meta_add_table to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_table to ' || a_user_name;
    execute immediate 'grant execute on meta_rename_table to ' || a_user_name;
    execute immediate 'grant execute on meta_add_string_column to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_string_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_column_nullability to ' || a_user_name;
    execute immediate 'grant execute on meta_set_string_max_length to ' || a_user_name;
    execute immediate 'grant execute on meta_rename_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_ref_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_ref_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_ref_target to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_ref_target to ' || a_user_name;
    execute immediate 'grant execute on meta_set_ref_checking to ' || a_user_name;    
    execute immediate 'grant execute on meta_add_enum_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_enum_member to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_enum_member to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_member_value to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_value_checking to ' || a_user_name;
    execute immediate 'grant execute on meta_add_decimal_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_precision to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_scale to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_datetime_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_bool_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_bool_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_binary_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_big_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_small_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_guid_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_guid_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_text_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_index to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_index to ' || a_user_name;
    
    --Restoring table rows
    execute immediate 'grant execute on meta_restore_table_row to ' || a_user_name;
    --Hard deleting table rows
    execute immediate 'grant execute on meta_delete_table_row to ' || a_user_name;
    
    --Enabling/disabling recycle bin
    execute immediate 'grant execute on meta_set_recycle_bin to ' || a_user_name;
    
    --Granting all rights to all user tables
    for t in (select table_name from meta_tables) loop
        execute immediate 'grant select, insert, update, delete on tbl_' || t.table_name || ' to ' || a_user_name;
    end loop;
end;

--go

--Function of disabling schema change
create or replace procedure p_disable_schema_change(
    --User name
    a_user_name varchar2)
as
begin
    --Revoking rights

    --Update metadata version
    execute immediate 'revoke update on meta_version from ' || a_user_name;
    
    --Revoking update on meta tables
    execute immediate 'revoke update on meta_tables from ' || a_user_name;
    execute immediate 'revoke update on meta_string_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_ref_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_ref_targets from ' || a_user_name;
    execute immediate 'revoke update on meta_enum_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_enum_members from ' || a_user_name;
    execute immediate 'revoke update on meta_big_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_binary_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_bool_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_datetime_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_decimal_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_guid_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_small_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_text_columns from ' || a_user_name;
    
    --Grant execute on schema changing functions
    execute immediate 'revoke execute on meta_add_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_rename_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_string_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_string_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_column_nullability from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_string_max_length from ' || a_user_name;
    execute immediate 'revoke execute on meta_rename_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_ref_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_ref_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_ref_target from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_ref_target from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_ref_checking from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_enum_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_enum_member from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_enum_member from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_member_value from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_value_checking from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_decimal_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_precision from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_scale from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_datetime_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_bool_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_bool_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_binary_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_big_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_small_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_guid_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_guid_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_text_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_index from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_index from ' || a_user_name;
    
    --Restoring table rows
    execute immediate 'revoke execute on meta_restore_table_row from ' || a_user_name;
    --Hard deleting table rows
    execute immediate 'revoke execute on meta_delete_table_row from ' || a_user_name;
    
    --Enabling/disabling recycle bin
    execute immediate 'revoke execute on meta_set_recycle_bin from ' || a_user_name;
    
    --Revoking all rights from all user tables
    for t in (select table_name from meta_tables) loop
        execute immediate 'revoke select, insert, update, delete on tbl_' || t.table_name ||  ' from ' || a_user_name;
    end loop;
end;

--go

--Function of setting user's ability to change security
create or replace procedure meta_set_user_change_security(
    --User name
    a_user_name varchar2,
    --Can change security
    a_can_change_security number)
as
    --Old CanChangeSecurity
    a_old_can_change_security number(1);
    --Current user CanChangeSecurity
    a_curr_user_change_security number(1);
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20043,
                'Changing current user security change permission is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20043)');
    end if;
    
    --Searching for current user
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(user);
    
    --If current user was found
    if a_row_count <> 0 then
        --Reading current user ability to change security
        select can_change_security
        into a_curr_user_change_security
        from meta_users
        where user_name = lower(user);
        
        if a_curr_user_change_security = 0 then
            raise_application_error(-20045,
                'Insufficient privileges to change another user security permissions (user_name = '
                || a_user_name
                || ') (#error_code = 20045)');
        end if;
    end if;

    --Reading old CanChangeSecurity
    select can_change_security
    into a_old_can_change_security
    from meta_users
    where user_name = lower(a_user_name);
    
    --If there is no need for change
    if a_can_change_security = a_old_can_change_security then
        return;
    end if;
    
    if a_can_change_security = 1 then
        p_enable_security_change(a_user_name);
    else
        p_disable_security_change(a_user_name);
    end if;
    
     --Changing the metadata
    update meta_users
    set can_change_security = a_can_change_security
    where user_name = lower(a_user_name);
end;

--go

--Function of enabling security change
create or replace procedure p_enable_security_change(
    --User name
    a_user_name varchar2)
as
begin
    --Granting read on security metadata
    execute immediate 'grant select on meta_users to ' || a_user_name;
    execute immediate 'grant select on meta_user_roles to ' || a_user_name;
    execute immediate 'grant select on meta_role_roles to ' || a_user_name;
    execute immediate 'grant select on meta_roles to ' || a_user_name;
    execute immediate 'grant select on meta_principals to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_unit_access to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_full_access to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_own_rows to ' || a_user_name;
    
    --Granting update on security metadata
    execute immediate 'grant update(display_name) on meta_users to ' || a_user_name;
    execute immediate 'grant update(unit_name, display_name) on meta_units to ' || a_user_name;
    execute immediate 'grant update(display_name) on meta_roles to ' || a_user_name;
    
    --Granting execute on security admin functions
    execute immediate 'grant execute on meta_add_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_add_unit_parent to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_unit_parent to ' || a_user_name;
    execute immediate 'grant execute on meta_add_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_login to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_change_schema to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user_in_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user_from_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_role_in_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_role_from_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_table_rights to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_table_rights to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_password to ' || a_user_name;
    execute immediate 'grant execute on meta_set_unit_security to ' || a_user_name;   

end;

--go

--Function of disabling user ability to change security
create or replace procedure p_disable_security_change(
    --User name
    a_user_name varchar2)
as
begin
    --Revoking read on security metadata
    execute immediate 'revoke select on meta_users from ' || a_user_name;
    execute immediate 'revoke select on meta_user_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_role_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_principals from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_unit_access from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_full_access from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_own_rows from ' || a_user_name;
    
    --Revoking update on security metadata
    execute immediate 'revoke update on meta_users from ' || a_user_name;
    execute immediate 'revoke update on meta_units from ' || a_user_name;
    execute immediate 'revoke update on meta_roles from ' || a_user_name;
    
    --Revoking execute on security admin functions
    execute immediate 'revoke execute on meta_add_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_unit_parent from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_unit_parent from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_login from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_change_schema from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user_in_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user_from_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_role_in_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_role_from_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_table_rights from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_table_rights from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_password from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_unit_security from ' || a_user_name; 
    
    --!!! No need to revoke execute from meta_set_user_change_security
    -- Otherwise there might be a deadlock
end;

--go

--Function of deleting a user
create or replace procedure meta_delete_user(
    --User name
    a_user_name varchar2)
as
    --Current index
    a_curr_index number;
    --Affected users
    a_affected_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20046,
                'Deleting current user is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20046)');
    end if;
    
    --Updating the metadata
    delete from meta_users
    where user_name = lower(a_user_name);
    
    --Deleting user acl
    delete from meta_table_acl
    where principal_name = lower(a_user_name);
end;

--go

--Function of getting user principals
create or replace function p_get_user_principals(
    --User name
    a_user_name varchar2)
    return meta_name_list
as
    --Name list
    a_name_list meta_name_list := meta_name_list();
    --Roles to search
    a_role_to_search_list meta_name_list := meta_name_list();
begin
    with user_roles as (
        select container_role_name
        from meta_user_roles
        where user_name = lower(a_user_name))
    select distinct principal
    bulk collect into a_name_list
    from (
        --The user name
        select a_user_name as principal from dual
        union        
        --The user roles
        select container_role_name as principal from user_roles
        union
        --That roles parents
        select container_role_name as principal
        from meta_role_roles
        start with role_name in (select container_role_name from user_roles)
        connect by prior container_role_name = role_name);
    
    return a_name_list;
end;

--go

--Function of adding user's unit
create or replace procedure meta_add_user_unit(
    --User name
    a_user_name varchar2,
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected new users
    a_affected_new_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    p_check_unit_exists(a_unit_id);

    --Searching for the same user unit
    select count(*) into a_row_count
    from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20052,
                'Specified user unit was already added (unit_id = '
                || a_unit_id || ', user_name = '
                || a_user_name
                || ') (#error_code = 20052)');
    end if;
    
    --Updating the metadata
    insert into meta_user_units (user_name, unit_id)
    values (a_user_name, a_unit_id);
    
    --Refreshing the user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of deleting user's unit
create or replace procedure meta_delete_user_unit(
    --User name
    a_user_name varchar2,
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected old users
    a_affected_old_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    p_check_unit_exists(a_unit_id);

    --Searching for the user unit
    select count(*) into a_row_count
    from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20053,
                'Specified user unit was not found (unit_id = '
                || a_unit_id || ', user_name = '
                || a_user_name
                || ') (#error_code = 20053)');
    end if;
    
    --Updating the metadata
    delete from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    --Refreshing the user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of changing user password
create or replace procedure meta_set_user_password(
    --User name
    a_user_name varchar2,
    --New password
    a_new_password varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);

    execute immediate 'alter user ' || a_user_name
    || ' identified by "' || a_new_password || '"';
end;

--go

--Function of changing current user password
create or replace procedure meta_set_curr_user_password(
    --Old password
    a_old_password varchar2,
    --New password
    a_new_password varchar2)
as
begin
    p_check_user_exists(user);

    execute immediate 'alter user ' || user
    || ' identified by "' || a_new_password || '" replace "' || a_old_password || '"';
end;

--go


--Function of enabling/disabling table unit level security
create or replace procedure meta_set_unit_security(
    --Table id
    a_table_id raw,
    --Unit level security
    a_unit_level_security numeric)
as
    --Table name
    a_table_name varchar2(26);
begin
     p_check_table_exists(a_table_id);
     
     a_table_name := p_get_table_name(a_table_id);
     
     --Recreating table view
     p_drop_view_if_exists(a_table_name);
     
     --Updating the metadata
     update meta_tables
     set unit_level_security = a_unit_level_security
     where table_id = a_table_id;
     
     p_create_table_view(a_table_id);
end;

--go

--103-----------------------------------------------------------------------------------------

--Function of checking principal existence
create or replace procedure p_check_principal_exists(
    --Principal name
    a_principal_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the principal
    select count(*) into a_row_count
    from meta_principals
    where principal_name = lower(a_principal_name);
    
    if a_row_count = 0 then
        raise_application_error(-20047,
                'A principal with the specified name was not found (principal_name = '
                || a_principal_name
                || ') (#error_code = 20047)');
    end if;
end;

--go

--Function of adding table access rights
create or replace procedure meta_add_table_rights(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2,
    --Access type
    a_access_type number,
    --Access level
    a_access_level number)
as
    --Table name
    a_table_name varchar2(26);
    --Row count
    a_row_count number;
begin
    p_check_table_exists(a_table_id);
    p_check_principal_exists(a_principal_name);
    
    --Searching for the same table rights
    select count(*) into a_row_count
    from meta_table_acl
    where table_id = a_table_id
    and principal_name = a_principal_name
    and access_type = a_access_type;
    
    if a_row_count > 0 then
        raise_application_error(-20048,
                'A table permission with the same access type was already added (#error_code = 20048)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    if a_access_type = 1 then
        execute immediate 'grant select on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 2 then
        execute immediate 'grant insert on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 3 then
        execute immediate 'grant update on ' || a_table_name || ' to ' || a_principal_name;
    elsif a_access_type = 4 then
        execute immediate 'grant delete on ' || a_table_name || ' to ' || a_principal_name;
    end if;
    
    --Updating the metadata
    insert into meta_table_acl(table_id, principal_name, access_type, access_level)
    values(a_table_id, lower(a_principal_name), a_access_type, a_access_level);
    
    --Updating principal acl matrix
    p_update_principal_acl_matrix(a_table_id, a_principal_name);    
end;

--go

--Function of updating principal acl matrix
create or replace procedure p_update_principal_acl_matrix(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2)
as
    --Affected user names
    a_affected_users meta_name_list;
begin
    a_affected_users := p_users_affected_by_principal(a_principal_name);
    
    for i in 1..a_affected_users.count loop
        p_update_user_acl_matrix(a_table_id, a_affected_users(i), a_principal_name);
    end loop;
end;

--go

--Function of updating user acl matrix for the specified principal
create or replace procedure p_update_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Principal name
    a_principal_name varchar2)
as
begin
    --Reading principal acl
    for acl in (select access_type, access_level
        from meta_table_acl
        where table_id = a_table_id
        and principal_name = lower(a_principal_name)) loop
        
        --If it is a full access right
        if acl.access_level = 6 then
            p_add_full_access_right(a_table_id, a_user_name, acl.access_type);
        --If it is a own rows access rights
        elsif acl.access_level = 1 then
            p_add_own_rows_access_right(a_table_id, a_user_name, acl.access_type);
        --If it is unit-level access
        else
            --Searching for the unit ids in the specified access level
            for u in (select column_value unit_id 
                from table(p_get_unit_ids_for_unit_right(a_user_name, acl.access_level))) loop                
                p_add_unit_access_right(a_table_id, a_user_name, u.unit_id, acl.access_type);
            end loop;        
        end if;
        
    end loop;
end;

--go

--Function of adding a unit-level access right
create or replace procedure p_add_unit_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --The unit id
    a_unit_id raw,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_unit_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and unit_id = a_unit_id
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting the access right
        insert into meta_table_acl_unit_access
        (table_id, user_name, unit_id, access_type)
        values (a_table_id, lower(a_user_name), a_unit_id, a_access_type);
    end if;
end;

--go

--Function of adding full access rights
create or replace procedure p_add_full_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_full_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting access right
        insert into meta_table_acl_full_access (table_id, user_name, access_type)
        values (a_table_id, lower(a_user_name), a_access_type);
    end if;
end;  
 
--go

--Function of adding own rows access right
create or replace procedure p_add_own_rows_access_right(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Row count
    a_row_count number;
begin
    --Searching for the access rights
    select count(*) into a_row_count
    from meta_table_acl_own_rows
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        --Inserting access right
        insert into meta_table_acl_own_rows (table_id, user_name, access_type)
        values (a_table_id, lower(a_user_name), a_access_type);
    end if;
end;  
 
--go

--Function of getting a list of units affected by the specified user unit-level access right
create or replace function p_get_unit_ids_for_unit_right(
    --User name
    a_user_name varchar2,
    --Access level
    a_access_level number)
return meta_id_list
as
    --Current unit id
    a_curr_unit_id raw(16);
    --The unit id list
    unit_id_list meta_id_list := new meta_id_list();
    --Local id list
    local_id_list meta_id_list := meta_id_list();
    --Current index
    a_curr_index number;
begin
  
    --Enumerating user units
    for user_unit in (select unit_id
        from meta_user_units
        where user_name = lower(a_user_name)) loop
        
        a_curr_unit_id := user_unit.unit_id;
        
        local_id_list.delete;
        
       --If scope is current unit
        if a_access_level = 2 then
            --Adding the current unit id to the list
            local_id_list.extend;
            local_id_list(local_id_list.last) := a_curr_unit_id;
        --If scope is current and child units
        elsif a_access_level = 3 then
            --Adding current and child units to the list
            local_id_list := p_get_unit_children(a_curr_unit_id);
        --If scope is current and parent units
        elsif a_access_level = 4 then
            --Adding current and parent units to the list
            local_id_list := p_get_unit_parents(a_curr_unit_id);
        --If scope is current, parent and child units
        elsif a_access_level = 5 then
            --Adding current, parent and child units to the list
            local_id_list := p_get_unit_parents_children(a_curr_unit_id);
        end if;
        
        --Copying local results
        a_curr_index := local_id_list.first;
        
        -- We are no checking unit ids for duplicates
        -- bacause it is ok
        while a_curr_index is not null loop
            unit_id_list.extend;
            unit_id_list(unit_id_list.last) := local_id_list(a_curr_index);
            
            a_curr_index := local_id_list.next(a_curr_index);
        end loop;

    end loop;
    
    return unit_id_list;
end;

--go

--Function of user affected by the principal
create or replace function p_users_affected_by_principal(
    --Principal name
    a_principal_name varchar2)
return meta_name_list
as
    --Result user list
    a_user_list meta_name_list := meta_name_list();
    --Principal type
    a_principal_type varchar(30);
begin
    
    --Reading principal type
    select principal_type into a_principal_type
    from meta_principals
    where principal_name = lower(a_principal_name);
    
    --If it is a user
    if a_principal_type = 'user' then
        a_user_list.extend;
        a_user_list(a_user_list.last) := a_principal_name;
        return a_user_list;
    end if;
    
    --If it is a role
    select distinct user_name
    bulk collect into a_user_list
    from meta_user_roles
    where container_role_name in
        --Selecting all child roles
        (select role_name
        from meta_role_roles
        start with container_role_name = lower(a_principal_name)
        connect by prior container_role_name = role_name
        union
        select lower(a_principal_name) from dual);
    
    return a_user_list;
end;

--go

--Function of deleting user acl matrix
create or replace procedure p_delete_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
begin
    --Deleting the unit-level access rights
    delete from meta_table_acl_unit_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    --Deleting the full access rights
    delete from meta_table_acl_full_access
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
    
    --Deleting the own row access rights
    delete from meta_table_acl_own_rows
    where table_id = a_table_id
    and user_name = lower(a_user_name)
    and access_type = a_access_type;
end;

--go

--Function of refreshing user acl
create or replace procedure p_refresh_user_acl(
    --User name
    a_user_name varchar2)
as
begin
    for t in (select table_id from meta_tables) loop
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 1);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 2);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 3);
        p_refresh_user_acl_matrix(t.table_id, a_user_name, 4);
    end loop;
end;

--go

--Function of refreshing users matrix acl
create or replace procedure p_refresh_user_acl_matrix(
    --Table id
    a_table_id raw,
    --User name
    a_user_name varchar2,
    --Access type
    a_access_type number)
as
    --Principal name list
    a_principal_list meta_name_list;
begin
    --Deleting old user acl matrix
    p_delete_user_acl_matrix(a_table_id, a_user_name, a_access_type);

    --Getting all user principals
    a_principal_list := p_get_user_principals(a_user_name);
    
    --Updating user acl matrix
    for i in 1..a_principal_list.count loop
        p_update_principal_acl_matrix(a_table_id, a_principal_list(i));
    end loop;
end;

--go

--Function of deleting table access rights
create or replace procedure meta_delete_table_rights(
    --Table id
    a_table_id raw,
    --Principal name
    a_principal_name varchar2,
    --Access type
    a_access_type number)
as
    --Table name
    a_table_name varchar2(26);
    --Row count
    a_row_count number;
    --Users affected by the principal
    a_affected_user_list meta_name_list;
begin
    p_check_table_exists(a_table_id);
    p_check_principal_exists(a_principal_name);
    
    --Searching for the table rights
    select count(*) into a_row_count
    from meta_table_acl
    where table_id = a_table_id
    and principal_name = a_principal_name
    and access_type = a_access_type;
    
    if a_row_count = 0 then
        raise_application_error(-20049,
                'Specified table permission was not found (#error_code = 20049)');
    end if;

    a_table_name := p_get_table_name(a_table_id);
    
     if a_access_type = 1 then
        execute immediate 'revoke select on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 2 then
        execute immediate 'revoke insert on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 3 then
        execute immediate 'revoke update on ' || a_table_name || ' from ' || a_principal_name;
    elsif a_access_type = 4 then
        execute immediate 'revoke delete on ' || a_table_name || ' from ' || a_principal_name;
    end if;
    
    --Getting affected by the principal users
    a_affected_user_list := p_users_affected_by_principal(a_principal_name);
    
    --Updating the metadata
    delete from meta_table_acl
    where table_id = a_table_id
    and principal_name = lower(a_principal_name)
    and access_type = a_access_type;
    
    --Updating users affected by the principal
    for i in 1..a_affected_user_list.count loop
        p_refresh_user_acl_matrix(a_table_id, a_affected_user_list(i), a_access_type);
    end loop;
    
end;

--go